<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		
	<span class="different-posts different-posts_earlier">📖 <a href="/page/28">earlier posts</a> 📖</span>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/26. Mysql数据库主从心得整理/">Mysql数据库主从心得整理</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="一、mysql主从的原理"><a href="#一、mysql主从的原理" class="headerlink" title="一、mysql主从的原理"></a>一、mysql主从的原理</h2><h3 id="1、Replication-线程"><a href="#1、Replication-线程" class="headerlink" title="1、Replication 线程"></a>1、Replication 线程</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql的 Replication 是一个异步的复制过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要实现 MySQL 的 Replication ，首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项，或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。</p>
<h3 id="2、MySQL-复制的基本过程如下："><a href="#2、MySQL-复制的基本过程如下：" class="headerlink" title="2、MySQL 复制的基本过程如下："></a>2、MySQL 复制的基本过程如下：</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;2.1．Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;2.2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置；</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;2.3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;2.4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。</p>
<h3 id="3、Mysql复制的几种模式"><a href="#3、Mysql复制的几种模式" class="headerlink" title="3、Mysql复制的几种模式"></a>3、Mysql复制的几种模式</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;3.1.从 MySQL 5.1.12 开始，可以用以下三种模式来实现：</p>
<ul>
<li>基于SQL语句的复制(statement-based replication, SBR)，</li>
<li>基于行的复制(row-based replication, RBR)，</li>
<li>混合模式复制(mixed-based replication, MBR)。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是默认的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在运行时可以动态改动 binlog的格式，除了以下几种情况：</p>
<ol>
<li>存储流程或者触发器中间</li>
<li>启用了NDB</li>
<li>当前会话试用 RBR 模式，并且已打开了临时表</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式：</p>
<ol>
<li>当DML语句更新一个NDB表时</li>
<li>当函数中包含 UUID() 时</li>
<li>2个及以上包含 AUTO_INCREMENT 字段的表被更新时</li>
<li>行任何 INSERT DELAYED 语句时</li>
<li>用 UDF 时</li>
<li>视图中必须要求运用 RBR 时，例如建立视图是运用了 UUID() 函数</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;3.2.设定主从复制模式：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">log</span>-bin=mysql-bin</div><div class="line"><span class="comment">#binlog_format="STATEMENT"</span></div><div class="line"><span class="comment">#binlog_format="ROW"</span></div><div class="line">binlog_format=<span class="string">"MIXED"</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也可以在运行时动态修改binlog的格式。例如</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'STATEMENT'</span>;</div><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'ROW'</span>;</div><div class="line">mysql&gt; SET SESSION binlog_format = <span class="string">'MIXED'</span>;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = <span class="string">'STATEMENT'</span>;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = <span class="string">'ROW'</span>;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = <span class="string">'MIXED'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;3.3.两种模式各自的优缺点：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SBR 的优点：</p>
<ul>
<li>历史悠久，技能成熟</li>
<li>binlog文件较小</li>
<li>binlog中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况</li>
<li>binlog可以用于实时的还原，而不仅仅用于复制</li>
<li>主从版本可以不一样，从服务器版本可以比主服务器版本高</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SBR 的缺点：</p>
<ul>
<li>不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。</li>
<li>调用具有不确定因素的 UDF 时复制也可能出疑问</li>
<li>运用以下函数的语句也不能被复制：<ul>
<li>LOAD_FILE()</li>
<li>UUID()</li>
<li>USER()</li>
<li>FOUND_ROWS()</li>
<li>SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)</li>
</ul>
</li>
<li>INSERT … SELECT 会产生比 RBR 更多的行级锁</li>
<li>复制须要执行 全表扫描(WHERE 语句中没有运用到索引)的 UPDATE 时，须要比 RBR 请求更多的行级锁</li>
<li>对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句</li>
<li>对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响</li>
<li>存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事</li>
<li>确定了的 UDF 也须要在从服务器上执行</li>
<li>数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错</li>
<li>执行复杂语句如果出错的话，会消耗更多资源</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;RBR 的优点：</p>
<ul>
<li>任何情况都可以被复制，这对复制来说是最安全可靠的</li>
<li>和其他大多数数据库系统的复制技能一样</li>
<li>多数情况下，从服务器上的表如果有主键的话，复制就会快了很多</li>
<li>复制以下几种语句时的行锁更少：<ul>
<li>INSERT … SELECT</li>
<li>包含 AUTO_INCREMENT 字段的 INSERT</li>
<li>没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句</li>
</ul>
</li>
<li>执行 INSERT，UPDATE，DELETE 语句时锁更少</li>
<li>从服务器上采用多线程来执行复制成为可能</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;RBR 的缺点：</p>
<ul>
<li>binlog 大了很多</li>
<li>复杂的回滚时 binlog 中会包含大量的数据</li>
<li>主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写疑问</li>
<li>UDF 产生的大 BLOB 值会导致复制变慢</li>
<li>不能从 binlog 中看到都复制了写什么语句(加密过的)</li>
<li>当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外，针对系统库 mysql 里面的表发生变化时的处理准则如下：</p>
<ul>
<li>如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录</li>
<li>如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：采用 RBR 模式后，能处理很多原先出现的主键重复问题。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;实例:对于insert into db_allot_ids select * from db_allot_ids 这个语句:</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在BINLOG_FORMAT=STATEMENT 模式下:</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;BINLOG日志信息为:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">BEGIN</div><div class="line">/*!*/;</div><div class="line"><span class="comment"># at 173</span></div><div class="line"><span class="comment">#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0</span></div><div class="line">SET TIMESTAMP=1244793942/*!*/;</div><div class="line">insert into db_allot_ids select * from db_allot_ids</div><div class="line">/*!*/;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在BINLOG_FORMAT=ROW 模式下:</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;BINLOG日志信息为:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">BINLOG <span class="string">'</span></div><div class="line"><span class="string">hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA</span></div><div class="line"><span class="string">hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=</span></div><div class="line"><span class="string">'</span>/*!*/;</div></pre></td></tr></table></figure>
<h3 id="4、Mysql主从的优缺点"><a href="#4、Mysql主从的优缺点" class="headerlink" title="4、Mysql主从的优缺点"></a>4、Mysql主从的优缺点</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL的主从同步是一个很成熟的架构，优点为：①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服 务器压力;②在从主服务器进行备份，避免备份期间影响主服务器服务;③当主服务器出现问题时，可以切换到从服务器。所以我在项目部署和实施中经常会采用这种方案;鉴于生产环境下的mysql的严谨性。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;实际上，在老版本中，MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同协作而完成的，而是由单独的一个线程来完成所有的工作。但是 MySQL 的工程师们很快发现，这样做存在很大的风险和性能问题，主要如下：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先，如果通过一个单一的线程来独立实现这个工作的话，就使复制 Master 端的，Binary Log日志，以及解析这些日志，然后再在自身执行的这个过程成为一个串行的过程，性能自然会受到较大的限制，这种架构下的 Replication 的延迟自然就比较长了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其次，Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后，需要接着解析这些内容，还原成 Master 端所执行的原始 Query，然后在自身执行。在这个过程中，Master端很可能又已经产生了大量的变化并生成了大量的 Binary Log 信息。如果在这个阶段 Master 端的存储系统出现了无法修复的故障，那么在这个阶段所产生的所有变更都将永远的丢失，无法再找回来。这种潜在风险在Slave 端压力比较大的时候尤其突出，因为如果 Slave 压力比较大，解析日志以及应用这些日志所花费的时间自然就会更长一些，可能丢失的数据也就会更多。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;所以，在后期的改造中，新版本的 MySQL 为了尽量减小这个风险，并提高复制的性能，将 Slave 端的复制改为两个线程来完成，也就是前面所提到的 SQL 线程和 IO 线程。最早提出这个改进方案的是Yahoo!的一位工程师“Jeremy Zawodny”。通过这样的改造，这样既在很大程度上解决了性能问题，缩短了异步的延时时间，同时也减少了潜在的数据丢失量。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当然，即使是换成了现在这样两个线程来协作处理之后，同样也还是存在 Slave 数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事务中，这些问题都是存在的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果要完全避免这些问题，就只能用 MySQL 的 Cluster 来解决了。不过 MySQL的 Cluster 知道笔者写这部分内容的时候，仍然还是一个内存数据库的解决方案，也就是需要将所有数据包括索引全部都 Load 到内存中，这样就对内存的要求就非常大的大，对于一般的大众化应用来说可实施性并不是太大。MySQL 现在正在不断改进其 Cluster 的实现，其中非常大的一个改动就是允许数据不用全部 Load 到内存中，而仅仅只是索引全部 Load 到内存中，我想信在完成该项改造之后的 MySQL Cluster 将会更加受人欢迎，可实施性也会更大。</p>
<h3 id="5、Mysql的半同步模式（Semisynchronous-Replication）"><a href="#5、Mysql的半同步模式（Semisynchronous-Replication）" class="headerlink" title="5、Mysql的半同步模式（Semisynchronous Replication）"></a>5、Mysql的半同步模式（Semisynchronous Replication）</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;我们知道在5.5之前，MySQL的复制其实是异步操作，而不是同步，也就意味着允许主从之间的数据存在一定的延迟，mysql当初这样设计的目的可能也是基于可用性的考虑，为了保证master不受slave的影响，并且异步复制使得master处于一种性能最优的状态：写完binlog后即可提交而不需要等待slave的操作完成。这样存在一个隐患，当你使用slave作为备份时，如果master挂掉，那么会存在部分已提交的事务未能成功传输到slave的可能，这就意味着数据丢失！</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在MySQL5.5版本中，引入了半同步复制模式（Semi-synchronous Replication）能够成功（只是相对的）避免上述数据丢失的隐患。在这种模式下：master会等到binlog成功传送并写入至少一个slave的relay log之后才会提交，否则一直等待，直到timeout（默认10s）。当出现timeout的时候，master会自动切换半同步为异步，直到至少有一个slave成功收到并发送Acknowledge，master会再切换回半同步模式。结合这个新功能，我们可以做到，在允许损失一定的事务吞吐量的前提下来保证同步数据的绝对安全，因为当你设置timeout为一个足够大的值的情况下，任何提交的数据都会安全抵达slave。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql5.5 版本支持半同步复制功能（Semisynchronous Replication），但还不是原生的支持，是通过plugin来支持的，并且默认是没有安装这个插件的。不论是二进制发布的，还是自己源代码编译的，都会默认生成这个插件，一个是针对master 的一个是针对slave的，在使用之前需要先安装这俩plugins。</p>
<h2 id="二、Mysql主从复制的过滤"><a href="#二、Mysql主从复制的过滤" class="headerlink" title="二、Mysql主从复制的过滤"></a>二、Mysql主从复制的过滤</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制的过滤主要有２种方式：</p>
<ol>
<li>在主服务器在把事件从进二制日志中过滤掉，相关的参数是:binlog_do_db和binlog_ignore_db。</li>
<li>在从服务器上把事件从中继日志中过滤掉，相关的参数是replicate_*。</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制只能扩展读取，不能扩展写入，对数据进行分区可以进行扩展写入。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制的优化：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在mysql复制环境中,有8个参数可以让我们控制,需要复制或需要忽略不进行复制的DB或table分别为:</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面二项需要在Master上设置：</p>
<ul>
<li>Binlog_Do_DB:设定哪些数据库需要记录Binlog</li>
<li>Binlog_Ignore_DB:设定哪里数据库不需要记录Binlog</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;优点是Master端的Binlog记录所带来的Io量减少，网络IO减少，还会让slave端的IO线程,SQL线程减少，从而大幅提高复制性能,</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;缺点是mysql判断是否需要复制某个事件不是根据产生该事件的查询所在的DB,而是根据执行查询时刻所在的默认数据库（也就是登录时指定的库名或运行”use database”中指定的DB）,只有当前默认DB和配置中所设定的DB完全吻合时IO线程才会将该事件读取给slave的IO线程.所以,如果在默认DB和设定须要复制的DB不一样的情况下改变了须要复制的DB中某个Table中的数据,该事件是不会被复制到Slave中去的,这样就会造成Slave端的数据和Master的数据不一致.同样,在默认的数据库下更改了不须要复制的数据库中的数据,则会被复制到slave端,当slave端并没有该数据库时,则会造成复制出错而停止。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面六项需要在slave上设置：</p>
<ul>
<li>Replicate_Do_DB:设定需要复制的数据库,多个DB用逗号分隔</li>
<li>Replicate_Ignore_DB:设定可以忽略的数据库.</li>
<li>Replicate_Do_Table:设定需要复制的Table</li>
<li>Replicate_Ignore_Table:设定可以忽略的Table</li>
<li>Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置。</li>
<li>Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以带通配符。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;优点是在slave端设置复制过滤机制,可以保证不会出现因为默认的数据库问题而造成Slave和Master数据不一致或复制出错的问题.</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;缺点是性能方面比在Master端差一些.原因在于:不管是否须要复制,事件都会被IO线程读取到Slave端,这样不仅增加了网络IO量,也给Slave端的IO线程增加了Relay Log的写入量。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; <strong>注：在实际的生产应用中发现，在mysql5.0以前的版本，mysql的这个过滤设置几乎是形同虚设，不起作用：不管你在主库或是从库上设置了忽略某个数据库或是表，他依然会进行同步，所以在做5.0以前版本的主从同步时，一定保持主从数据库的一致性，主上有的库或是表从上一定要有，否则在同步的过程会出错。</strong></p>
<h2 id="三、Mysql主从同步的配置"><a href="#三、Mysql主从同步的配置" class="headerlink" title="三、Mysql主从同步的配置"></a>三、Mysql主从同步的配置</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主库IP：192.168.1.2</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从库IP：192.168.1.3</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;添加一个用于主从同步的用户：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">GRANT REPLICATION SLAVE ON *.* TO <span class="string">'repl'</span>@<span class="string">'%'</span> IDENTIFIED BY ‘1q2w3e4r’;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果监控mysql主从的话，请加上一个super权限：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">GRANT SUPER, REPLICATION SLAVE ON *.* TO &apos;repl&apos;@&apos;%&apos; IDENTIFIED BY &apos;1q2w3e4r&apos;;</div></pre></td></tr></table></figure>
<h3 id="1、主库的配置"><a href="#1、主库的配置" class="headerlink" title="1、主库的配置"></a>1、主库的配置</h3><h4 id="1-1．mysql5-0以下版本的配置"><a href="#1-1．mysql5-0以下版本的配置" class="headerlink" title="1.1．mysql5.0以下版本的配置"></a>1.1．mysql5.0以下版本的配置</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改主库mysql配置配置文件，在[mysqld]段添加以下内容：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">server-id = 1</div><div class="line"><span class="built_in">log</span>-bin=/home/mysql/logs/binlog/bin-log</div><div class="line">max_binlog_size = 500M</div><div class="line">binlog_cache_size = 128K</div><div class="line">binlog-do-db = adb</div><div class="line">binlog-ignore-db = mysql</div><div class="line"><span class="built_in">log</span>-slave-updates</div></pre></td></tr></table></figure>
<h4 id="1-2-mysql5-0以上版本的配置"><a href="#1-2-mysql5-0以上版本的配置" class="headerlink" title="1.2. mysql5.0以上版本的配置"></a>1.2. mysql5.0以上版本的配置</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改主库mysql配置配置文件，在[mysqld]段添加以下内容：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">server-id = 1</div><div class="line"><span class="built_in">log</span>-bin=/home/mysql/logs/binlog/bin-log</div><div class="line">max_binlog_size = 500M</div><div class="line">binlog_cache_size = 128K</div><div class="line">binlog-do-db = adb</div><div class="line">binlog-ignore-db = mysql</div><div class="line"><span class="built_in">log</span>-slave-updates</div><div class="line">expire_logs_day=2</div><div class="line">binlog_format=<span class="string">"MIXED"</span></div></pre></td></tr></table></figure>
<h4 id="1-3-各个参数的含义和相关注意项："><a href="#1-3-各个参数的含义和相关注意项：" class="headerlink" title="1.3.各个参数的含义和相关注意项："></a>1.3.各个参数的含义和相关注意项：</h4><ul>
<li>server-id = 1 #服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。</li>
<li>log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log，并指定文件目录和文件名前缀。</li>
<li>max_binlog_size = 500M #每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。</li>
<li>binlog_cache_size = 128K #日志缓存大小</li>
<li>binlog-do-db = adb #需要同步的数据库名字，如果是多个，就以此格式在写一行即可。</li>
<li>binlog-ignore-db = mysql  #不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。</li>
<li>log-slave-updates  #当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。</li>
<li>expire_logs_day=2 #设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。</li>
<li>binlog_format=”MIXED”   #设置bin-log日志文件格式为：MIXED，可以防止主键重复。</li>
</ul>
<h3 id="2、从库的配置"><a href="#2、从库的配置" class="headerlink" title="2、从库的配置"></a>2、从库的配置</h3><h4 id="2-1-mysql5-1-7以前版本"><a href="#2-1-mysql5-1-7以前版本" class="headerlink" title="2.1.mysql5.1.7以前版本"></a>2.1.mysql5.1.7以前版本</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改从库mysql配置配置文件，在[mysqld]段添加以下内容：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line">server-id=2</div><div class="line">master-host=192.168.1.2</div><div class="line">master-user=repl</div><div class="line">master-password=1q2w3e4r</div><div class="line">master-port=3306</div><div class="line">master-connect-retry=30</div><div class="line">slave-skip-errors=1062</div><div class="line">replicate-do-db = adb</div><div class="line">replicate-ignore-db = mysql</div><div class="line">slave-skip-errors=1007,1008,1053,1062,1213,1158,1159</div><div class="line">master-info-file = /home/mysql/logs/master.info</div><div class="line">relay-log = /home/mysql/logs/relay-bin</div><div class="line">relay-log-index = /home/mysql/logs/relay-bin.index</div><div class="line">relay-log-info-file = /home/mysql/logs/relay-log.info</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果修改了连接主库相关信息，重启之前一定要删除master.info文件，否则重启之后由于连接信息改变从库而不会自动连接主库，造成同步失败。此文件是保存连接主库信息的。</p>
<h4 id="2-2-mysql5-1-7以后版本"><a href="#2-2-mysql5-1-7以后版本" class="headerlink" title="2.2.mysql5.1.7以后版本"></a>2.2.mysql5.1.7以后版本</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql5.1.7版本在丛库上面的配置很少，主要是采用了新的同步信息记录方式，他不在支持在配置文件中配置连接主库的相关信息，而是把连接等相关信息记录在master-info-file = /home/mysql/logs/master.info文件中，如果入库变了，直接在mysql命令行执行连接信息的改变即可生效，比较灵活了，而不用去重启mysql。修改从库mysql配置配置文件，在[mysqld]段添加以下内容：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">slave-skip-errors=1007,1008,1053,1062,1213,1158,1159</div></pre></td></tr></table></figure>
<h4 id="2-3-各个参数的含义和相关注意项"><a href="#2-3-各个参数的含义和相关注意项" class="headerlink" title="2.3.各个参数的含义和相关注意项"></a>2.3.各个参数的含义和相关注意项</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里只讲一下2个参数，其他全部是从库连接主库的信息和中间日志relay-log的设置。</p>
<ul>
<li>master-connect-retry=30 #这个选项控制重试间隔，默认为60秒。</li>
<li>slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #这个是在同步过程中忽略掉的错误，这些错误不会影响数据的完整性，有事经常出现的错误，一般设置忽略。其中1062为主键重复错误。</li>
</ul>
<h3 id="3、实现主从同步"><a href="#3、实现主从同步" class="headerlink" title="3、实现主从同步"></a>3、实现主从同步</h3><h4 id="3-1-实现数据库的统一"><a href="#3-1-实现数据库的统一" class="headerlink" title="3.1.实现数据库的统一"></a>3.1.实现数据库的统一</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;检查主从数据库的配置文件，查看是否已正确配置。首次实现 同步要备份主库上需要同步的数据库，然后完整的导入到从库中。注：mysql5.0之前的版本涉及到mysql本身复制过滤存在问题，需要把所有的数据库都备份导入到丛库，保持。</p>
<h4 id="3-2-查看并记录主库bin-log信息"><a href="#3-2-查看并记录主库bin-log信息" class="headerlink" title="3.2.查看并记录主库bin-log信息"></a>3.2.查看并记录主库bin-log信息</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入主库mysql中，执行：show master status;显示信息如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div><div class="line">+-------------+----------+--------------+------------------+</div><div class="line">| File        | Position | Binlog_do_db | Binlog_ignore_db |</div><div class="line">+-------------+----------+--------------+------------------+</div><div class="line">| bin-log.003 | 4        | adb          | mysql            |</div><div class="line">+-------------+----------+--------------+------------------+</div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;记录File 和Position信息；</p>
<h4 id="3-3-在从库上执行同步语句"><a href="#3-3-在从库上执行同步语句" class="headerlink" title="3.3.在从库上执行同步语句"></a>3.3.在从库上执行同步语句</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入mysql，执行以下语句：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">slave stop;</div><div class="line">change master to</div><div class="line">master_host=<span class="string">'192.168.1.2'</span>,</div><div class="line">master_user=<span class="string">'repl'</span>,</div><div class="line">master_password=<span class="string">'1q2w3e4r'</span>,</div><div class="line">master_port=3306,</div><div class="line">master_log_file=<span class="string">'bin-log.003'</span>,</div><div class="line">master_log_pos=4;</div><div class="line">slave start;</div></pre></td></tr></table></figure>
<h4 id="3-4-查看主从同步状态"><a href="#3-4-查看主从同步状态" class="headerlink" title="3.4.查看主从同步状态"></a>3.4.查看主从同步状态</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入mysql，执行show slave status\G;显示如下（mysql版本不同查询的结果不同，但是重要的指标还是一样的）：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql5.0之前的版本如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%BB%E4%BB%8E%E5%BF%83%E5%BE%97%E6%95%B4%E7%90%86/01.jpeg?raw=true" alt=""></figure> </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql5.5之前的版本如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%BB%E4%BB%8E%E5%BF%83%E5%BE%97%E6%95%B4%E7%90%86/02.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql5.5的版本如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%BB%E4%BB%8E%E5%BF%83%E5%BE%97%E6%95%B4%E7%90%86/03.jpeg?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重要的指标为：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div><div class="line">Master_Log_File: bin-log.003</div><div class="line">Relay_Master_Log_File: bin-log.003</div><div class="line">Read_Master_Log_Pos: 4</div><div class="line">Exec_master_log_pos: 4</div><div class="line">Seconds_Behind_Master: 0（5.0之前版本没有这个选项）</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上选项是两两对应的，只要结果是一致的，就说明主从同步成功。</p>
<h4 id="3-5-同步中的常见的错误和处理"><a href="#3-5-同步中的常见的错误和处理" class="headerlink" title="3.5.同步中的常见的错误和处理"></a>3.5.同步中的常见的错误和处理</h4><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;1、现象：在从库上面show slave status\G;出现下列情况，</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: No</div><div class="line">Seconds_Behind_Master: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;原因：</p>
<ul>
<li>程序可能在slave上进行了写操作；</li>
<li>也可能是slave机器重起后，事务回滚造成的；</li>
<li>有可能是在同步过程中遇到某种错误，这个会在查看从库中状态时看到错误提示，最少见的就是主键重复1062的错误。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入master</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div><div class="line">+----------------------+----------+--------------+------------------+</div><div class="line">| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |</div><div class="line">+----------------------+----------+--------------+------------------+</div><div class="line">| mysql-bin.000040     | 324      |adb           | mysql            |</div><div class="line">+----------------------+----------+--------------+------------------+</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后到slave服务器上执行手动同步</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">slave stop;</div><div class="line">change master to</div><div class="line">master_host=<span class="string">'10.14.0.140'</span>,</div><div class="line">master_user=<span class="string">'repl'</span>,</div><div class="line">master_password=<span class="string">'1q2w3e4r'</span>,</div><div class="line">master_port=3306,</div><div class="line">master_log_file=<span class="string">'mysql-bin.000040'</span>,</div><div class="line">master_log_pos=324;</div><div class="line">slave start;</div><div class="line">show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;2、现象：从数据库无法同步，show slave status显示:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: No</div><div class="line">Slave_SQL_Running: Yes</div><div class="line">Seconds_Behind_Master: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决：首先查看数据库的err日志，查看是什么错误提示，看从库连接主库的IP、用户、密码等相关信息是否有误，如果有误，重新执行同步；如果确认无误，重启主数据库。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div><div class="line">+------------------+----------+--------------+------------------+</div><div class="line">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |</div><div class="line">+------------------+----------+--------------+------------------+</div><div class="line">| mysql-bin.000001 | 98 | adb| mysql|</div><div class="line">+------------------+----------+--------------+------------------+</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入从库mysql，执行：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">slave stop;</div><div class="line">change master to Master_Log_File=<span class="string">'mysql-bin.000001'</span>,Master_Log_Pos=98;</div><div class="line">slave start;</div><div class="line">或是这样：</div><div class="line">stop slave;</div><div class="line"><span class="built_in">set</span> global sql_slave_skip_counter =1;</div><div class="line">start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个现象主要是master数据库存在问题，由于连接主库信息错误、主库数据库挂掉如果说常见错等原因引起的，我在实际的操作中先重启master后重启slave即可解决这问题，出现此问题，必须要要重启master数据库。</p>
<h2 id="四、mysql主主和主主集群"><a href="#四、mysql主主和主主集群" class="headerlink" title="四、mysql主主和主主集群"></a>四、mysql主主和主主集群</h2><h3 id="1、mysql主主的实现"><a href="#1、mysql主主的实现" class="headerlink" title="1、mysql主主的实现"></a>1、mysql主主的实现</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在实际的生产应用中，为了在主库出现崩溃或是主服务器出现严重故障时快速的恢复业务，会直接切换到从库上，当主库故障处理完成后让他直接作为丛库来运行，此时主主就是一个不错的选择。</p>
<h2 id="五、mysql主从的监控"><a href="#五、mysql主从的监控" class="headerlink" title="五、mysql主从的监控"></a>五、mysql主从的监控</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在mysql主从的应用中，只要进行了合理设置，基本上不会出现问题，但是对他的监控是必不可少的，以免由于真的出现问题又不知道而造成不必要的数据损失。</p>
<h3 id="1、mysql主从监控的主要思路"><a href="#1、mysql主从监控的主要思路" class="headerlink" title="1、mysql主从监控的主要思路"></a>1、mysql主从监控的主要思路</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql主从的监控，其主要是监控从库上的一些重要参数：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div><div class="line">Master_Log_File: bin-log.003</div><div class="line">Relay_Master_Log_File: bin-log.003</div><div class="line">Read_Master_Log_Pos: 4</div><div class="line">Exec_master_log_pos: 4</div><div class="line">Seconds_Behind_Master: 0（5.0之前版本没有这个选项）</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;通过以上的参数可以反映出主库和从库状态是否正常，从库是否落后于主库等。值得一提的是在mysql5.0以前的版本，Slave_IO_Running这个状态指标不可靠，会在主库直接挂掉的情况下不会变成NO，Seconds_Behind_Master参数也不存在。监控以上参数即可监控mysql主从。</p>
<h3 id="2、mysql主从监控的实现"><a href="#2、mysql主从监控的实现" class="headerlink" title="2、mysql主从监控的实现"></a>2、mysql主从监控的实现</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不管mysql是那个版本，其中的从库上的Exec_master_log_pos、Exec_master_log_pos；主库上的 Master上的Log_File， Position，这四个参数可以判断出当前主从的状态。以下是适用于mysql所有版本的主从监控shell脚本：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#/bin/sh</span></div><div class="line">user=repl</div><div class="line">passwd=123415</div><div class="line">master_ip=<span class="string">"192.168.1.2"</span></div><div class="line"><span class="built_in">log</span>=<span class="string">"/data3/check_repl.log"</span></div><div class="line">value()</div><div class="line">&#123;</div><div class="line"> master=`/usr/<span class="built_in">local</span>/mysql/bin/mysql -u<span class="variable">$user</span> -p<span class="variable">$passwd</span> -h<span class="variable">$master_ip</span> -e <span class="string">"show master status\G;"</span>|egrep <span class="string">"File|Position"</span>`</div><div class="line"> <span class="comment">#mysql 4.0</span></div><div class="line"> slave=`/usr/<span class="built_in">local</span>/mysql/bin/mysql -u<span class="variable">$user</span> -p<span class="variable">$passwd</span> -h127.0.0.1 -e <span class="string">"show slave status\G;"</span>|egrep <span class="string">"Relay_Master_Log_File|Exec_master_log_pos"</span>`</div><div class="line"> <span class="comment">#mysql 5.0</span></div><div class="line"> <span class="comment">#slave=`mysql -u$user -p$passwd -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"`</span></div><div class="line"> <span class="comment">#取主库上的bin-log号及写入的当前日志位置   </span></div><div class="line"> Master_Log=`<span class="built_in">echo</span> <span class="variable">$master</span> |awk <span class="string">'&#123;print $2&#125;'</span>|awk -F <span class="string">"."</span> <span class="string">'&#123;print $2&#125;'</span>`</div><div class="line"> Master_Log_Pos=`<span class="built_in">echo</span> <span class="variable">$master</span> |awk <span class="string">'&#123;print $4&#125;'</span>`</div><div class="line"> <span class="comment">#取从库上当前同步主库的位置</span></div><div class="line"> Relay_Master_Log_File=`<span class="built_in">echo</span> <span class="variable">$slave</span> |awk <span class="string">'&#123;print $2&#125;'</span>|awk -F <span class="string">"."</span> <span class="string">'&#123;print $2&#125;'</span>`</div><div class="line"> Exec_Master_Log_Pos=`<span class="built_in">echo</span> <span class="variable">$slave</span> |awk <span class="string">'&#123;print $4&#125;'</span>`</div><div class="line"> <span class="built_in">echo</span> <span class="string">"Master_Log:"</span><span class="variable">$Master_Log</span>&gt;&gt;<span class="variable">$log</span></div><div class="line"> <span class="built_in">echo</span> <span class="string">"Master_Log_Pos:"</span><span class="variable">$Master_Log_Pos</span>&gt;&gt;<span class="variable">$log</span></div><div class="line"> <span class="built_in">echo</span> <span class="string">"Relay_Master_Log_File:"</span><span class="variable">$Relay_Master_Log_File</span>&gt;&gt;<span class="variable">$log</span></div><div class="line"> <span class="built_in">echo</span> <span class="string">"Exec_Master_Log_Pos:"</span><span class="variable">$Exec_Master_Log_Pos</span>&gt;&gt;<span class="variable">$log</span></div><div class="line">&#125;</div><div class="line"><span class="keyword">for</span>((i=1;i&lt;=10;i++));</div><div class="line"><span class="keyword">do</span></div><div class="line"> <span class="built_in">echo</span> <span class="string">"#################################"</span>&gt;&gt;<span class="variable">$log</span></div><div class="line"> value</div><div class="line"> time=`date +<span class="string">"%Y-%m-%d %H:%M:%S"</span>`</div><div class="line"> <span class="keyword">if</span> [ <span class="variable">$Master_Log</span> -eq <span class="variable">$Relay_Master_Log_File</span> ];<span class="keyword">then</span></div><div class="line">       A=`expr <span class="variable">$Master_Log_Pos</span> - <span class="variable">$Exec_Master_Log_Pos</span>`</div><div class="line">       <span class="keyword">if</span> [ <span class="variable">$A</span> -lt 0 ];<span class="keyword">then</span></div><div class="line">             A=`expr 0 - <span class="variable">$A</span>`</div><div class="line">       <span class="keyword">fi</span></div><div class="line">       <span class="built_in">echo</span> <span class="variable">$A</span>&gt;&gt;<span class="variable">$log</span></div><div class="line">       <span class="keyword">if</span> [ <span class="variable">$A</span> -lt 10000 ];<span class="keyword">then</span></div><div class="line">             <span class="built_in">echo</span> <span class="string">"<span class="variable">$time</span> Master-Slave is OK."</span>&gt;&gt;<span class="variable">$log</span></div><div class="line">             <span class="comment">#echo "$i"</span></div><div class="line">             <span class="built_in">break</span></div><div class="line">       <span class="keyword">else</span></div><div class="line">             <span class="keyword">if</span> [ <span class="variable">$i</span> ge 3 ];<span class="keyword">then</span>              </div><div class="line">                  <span class="built_in">echo</span> <span class="string">"<span class="variable">$time</span> Warning:Slave-Master lag <span class="variable">$A</span> "</span> &gt;&gt;<span class="variable">$log</span></div><div class="line">                  <span class="built_in">echo</span> <span class="string">"<span class="variable">$i</span>"</span></div><div class="line">             <span class="keyword">fi</span></div><div class="line">             sleep 30</div><div class="line">             <span class="built_in">continue</span></div><div class="line">       <span class="keyword">fi</span></div><div class="line"> <span class="keyword">else</span></div><div class="line">       sleep 60</div><div class="line">       <span class="keyword">fi</span></div><div class="line">       <span class="keyword">if</span> [ <span class="variable">$i</span> -eq 10 ];<span class="keyword">then</span></div><div class="line">             <span class="built_in">echo</span> <span class="string">"<span class="variable">$i</span>"</span></div><div class="line">             <span class="built_in">echo</span> <span class="string">"<span class="variable">$time</span> Error:Slave-Master must be check !"</span> &gt;&gt;<span class="variable">$log</span></div><div class="line">       <span class="keyword">fi</span></div><div class="line"><span class="keyword">done</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在mysql5.0以后的版本，mysql主从已经相当的成熟了，可以只监控Slave_IO_Running，Slave_SQL_Running，Seconds_Behind_Master状态就可以了，这里不再做说明。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/24. mysql主从同步问题集/">mysql 主从同步问题集</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在InnoDB引擎下发现，Mysql的主从热备存在数据不一致的问题，一些数据没有成功同步到备机。在use databases后，更新的表必须是当前选择的database才同步。譬如连上Mysql服务后操作： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">USE test2; </div><div class="line">UPDATE client SET name=<span class="string">'test'</span> WHERE uid=1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;数据未能同步到备机，而使用use  test后，才可以成功同步，如下方式： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">USE <span class="built_in">test</span>; </div><div class="line">UPDATE client SET name=<span class="string">'test'</span> WHERE uid=1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;仔细看Mysql手册，发现同步启动选项中还有玄机，只设置replicate-do-db指定同步数据库还不够，是没有设置replicate-wild-do-table导致的跨库同步问题。Mysql默认是同步指定数据库下的更新操作，若要跨库操作更新同步，就必须指定replicate-wild-do-table参数。下面是Mysql手册中对replicate-do-db和replicate-wild-do-table启动选项的说明： </p>
<blockquote>
<p>–replicate-do-db=db_name<br>告诉slave只同步那些缺省数据库是 db_name (也就是用 USE 选中的)的语句。想要指定更多的数据库，只需多次使用该选项，每次指定一个数据库。注意，类似 UPDATE some_db.some_table SET foo=’bar’ 这样的跨库操作语句以及没有选中数据库的操作都不会被同步。如果必须使用跨库操作，要确保使用MySQL 3.23.28或更高，并且使用 –replicate-wild-do-table=db_name.% 选项。请仔细阅读最后面的注意事项。 </p>
</blockquote>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面是一个不能按照预期工作的例子：如果启动slave时使用 –replicate-do-db=sales 选项，并且在master上执行下列语句，那么这个 UPDATE 语句不会被同步： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">USE prices; </div><div class="line">UPDATE sales.january SET amount=amount+1000;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果需要同步跨库操作，只需使用 –replicate-wild-do-table=db_name.% 选项。这个”只检查缺省数据库”特性的主要原因是因为想要单从一个语句中判断是否要被同步比较困难(例如，使用多表 DELETE 或者 UPDATE，这就跨库了)。不过想要检查是否是缺省数据库却很快。 </p>
<blockquote>
<p>–replicate-wild-do-table=db_name.tbl<em>name<br>限制slave只同步那些匹配指定模式的数据表。模式中可以包含通配符 <code>%</code> 和 `</em>`，它们的含义和 LIKE 模式一样。想要指定更多的数据表，只需多次使用该选项，每次指定一个数据表。请仔细阅读最后面的注意事项。 </p>
</blockquote>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;例如： –replicate-wild-do-table=foo%.bar% 会同步所有以 foo 开头的数据库下的以 bar 开头的数据表上的更新操作。 如果匹配模式是 %，则匹配所有的表名，且应用到数据库级语句(CREATE DATABASE, DROP DATABASE,和 ALTER DATABASE)。例如，使用 –replicate-wild-do-table=foo%.% 选项的话，所有匹配 foo% 模式的数据库级操作都会被同步。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果想要在数据库/表模式中包含原义通配符，需要用反斜杠来转义它们。例如，想要同步 my<em>own%db 数据库下的所有表，但是不想同步 my1ownAABCdb 数据库下的表，就需要转义字符 `</em>`： –replicate-wild-do-table=my\_own\%db。如果是在命令行中使用这个选项，就可能需要两个反斜杠来转义，这依赖于命令行解释器。例如，在 bash shell下，就需要输入： –replicate-wild-do-table=my\\_own\\%db。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还存在一些问题 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不管有没有replicate-wild-do-table选项，更新操作必须是mysql连接已经有选择的数据库了才进行，譬如新建的mysql(新连接是没有默认选择的database的)连接中执行： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">UPDATE test.client SET name=<span class="string">'test'</span> WHERE uid=1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这条更新无法同步到备机，必须在update前use database操作，该database必须是replicate-wild-do-table中指定的database。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;保险的解决方式：连接上mysql后，调用mysql_select_db()选择数据库，之后进行的更新操作就可以自动同步了。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SQL模式匹配 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在my.cnf中設定master是錯誤的，你会发现在设置好重启后并没有按照之前的方式来运行。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">master-host            = 192.168.10.2 </div><div class="line">master-user            =  repl </div><div class="line">master-password = slavepass </div><div class="line">master-port            = 3306</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;请在mysql下执行底下指令</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">mysql&gt;CHANGE MASTER TO </div><div class="line">MASTER_HOST=’192.168.10.2′, </div><div class="line">MASTER_USER=’repl’, </div><div class="line">MASTER_PASSWORD=’slavepass’;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看主从服务状态的指令：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G </div><div class="line">mysql&gt; show master status\G </div><div class="line">mysql&gt; show master logs;</div></pre></td></tr></table></figure>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql主从复制碰到(server_errno=1236)解决过程 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;今天上论坛发现新发表的帖子无法正常显示(论坛数据库采用Mysql主从复制进行读写分离方案)，马上想到可能主从复制同步上出现问题，同一时间收到同事消息说数据库的主复制出现故障重启了，这时找到事故原因主数据库重启了会导致从数据库数据复制同步上延后，过一段时间SSH到从数据库上show slave status\G;查看状态显示 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"> Slave_IO_Running: NO </div><div class="line"> Slave_SQL_Running: Yes </div><div class="line">... </div><div class="line"> Last_Errno: 0 </div><div class="line"> Last_Error: </div><div class="line">... </div><div class="line">Seconds_Behind_Master: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从状态信息来看数据同步没有延后也没有报任何的错误，但Slave_IO_Running: NO显示同步IO进程失败。根据以往经验会先重启一下Slave后在show slave status\G;看一下是否会恢复正常，Slave_IO_Running: NO问题还是没有解决于是查看一下Mysql的错误日志发现： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">090605  9:13:20 [Note] Slave SQL thread initialized, starting replication <span class="keyword">in</span> <span class="built_in">log</span> ‘mysql-bin.000102′ at position 1029244974, relay <span class="built_in">log</span> ‘./xxx-relay-bin.000634′ position: 98 </div><div class="line">090605  9:13:20 [Note] Slave I/O thread: connected to master ’slave163@192.168.0.131:3306′,  replication started <span class="keyword">in</span> <span class="built_in">log</span> ‘mysql-bin.000102′ at position 1029244974 </div><div class="line">090605  9:13:20 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) </div><div class="line">090605  9:13:20 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary <span class="built_in">log</span> </div><div class="line">090605  9:13:20 [Note] Slave I/O thread exiting, <span class="built_in">read</span> up to <span class="built_in">log</span> ‘mysql-bin.000102′, position 1029244974 </div><div class="line">090605  9:13:52 [Note] Error reading relay <span class="built_in">log</span> event: slave SQL thread was killed</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在网上查了很多资料可能因为mysql-bin.000102日志文件中并没有1029244974这个位置，vi打开mysql-bin.000102日志文件搜索1029244974确实没有搜到内容，但为什么主数据库意外的重启会导致Slave上读取位置的错误，记得以前主数据库也手动重启过，Slave上也没有出现过这种问题。解决方法是使用CHANGE MASTER TO命令就是让Slave跳过mysql-bin.000102日志文件1029244974这个位置直接到下一个日志文件： </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">SLAVE STOP; </div><div class="line">CHANGE MASTER TO MASTER_LOG_FILE=<span class="string">'mysql-bin.000103'</span>, MASTER_LOG_POS=0; </div><div class="line">SLAVE START;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;但是这样如果mysql-bin.000102日志文件的内容没有同步完会导致主数据库与从数据库的内容不一致，那何不把位置向前移呢。 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">SLAVE STOP; </div><div class="line">CHANGE MASTER TO MASTER_LOG_FILE=<span class="string">'mysql-bin.000102'</span>, MASTER_LOG_POS=1019244974; </div><div class="line">SLAVE START;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;于是试了一下，果然Slave_IO_Running: YES同步成功跳过了mysql-bin.000102日志文件1029244974这个位置并继续读取下一个位置，这样从数据库也不会丢失数据和主数据库保持数据的一致，如果my.cnf中没有加slave-skip-errors参数跳过一些错误的话，同步位置向前移会导致一些数据重新插入到表中报主键重复错误加上参数就可以成功跳过这些错误了。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;今天一台数据库的slave报 Slave_IO_Running: No的错误, 登陆上机器执行. </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">&gt;slave stop; </div><div class="line">&gt;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; </div><div class="line">&gt;slave start;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看看slave的状态, 依然是Slave_IO_Running: No </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看看mater错误日志, 发现有一段奇怪的日志如下: </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">Got timeout reading communication packets</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看看master的错误日志, 那就更奇怪了: </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">090430 15:49:38 [Note] Slave I/O thread: connected to master <span class="string">'user@192.16.0.123:3306'</span>,replication started <span class="keyword">in</span> <span class="built_in">log</span> <span class="string">'xxx-bin.000815'</span> at position 3776386 </div><div class="line">090430 15:49:38 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) </div><div class="line">090430 15:49:38 [ERROR] Got fatal error 1236: <span class="string">'Client requested master to start replication from impossible position'</span> from master when reading data from binary <span class="built_in">log</span> </div><div class="line">090430 15:49:38 [Note] Slave I/O thread exiting, <span class="built_in">read</span> up to <span class="built_in">log</span> <span class="string">'xxx-bin.000815'</span>, position 3776386</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可能是xxx-bin.000815这个文件有问题, 看了一下它的大小，果然没有3776386这个位置，slave读的时候肯定是错误了， 到底为什么会这样就不清楚了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决办法就是读取下一个bin-log了 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">&gt;slave stop; </div><div class="line">&gt;CHANGE MASTER TO MASTER_LOG_FILE=<span class="string">'xxx-bin.000816'</span>,MASTER_LOG_POS=0; </div><div class="line">&gt;slave start; </div><div class="line">&gt;show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以看到Slave_IO_Running: Yes, 问题解决.</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从库配置文件my2.ini </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">port=3307 </div><div class="line">datadir=”” </div><div class="line">server-id=2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启用从库日志，这样可以进行链式复制 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">log</span>-slave-updates</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从库是否只读，0表示可读写，1表示只读 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">read</span>-only=1</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只复制某个表 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-do-table=tablename</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只复制某些表(可用匹配符) </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-wild-do-table=tablename%</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只复制某个库 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-do-db=dbname</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只复制某些库 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicte-wild-do-db=dbname%</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不复制某个表 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-ignore-table=tablename</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不复制某些表 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-wild-ignore-table=tablename%</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不复制某个库 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">replicate-ignore-db=dbname</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制完的sql语句是否立即从中继日志中清除，1表示立即清除 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">relay-log-purge=1</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从服务器主机，用于show slave hosts生成从库清单 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">report-host=hostname</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从库的数据目录下，有几个和复制相关的文件需要说明一下： </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;*-reloay-bin.* 从主库同步过来的Bin log文件，也叫中继日志 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;master.info 主库帐号信息和同步信息，这里记录了复制用户名和密码，需要保护好权限。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;relay-log.info 跟踪执行同步过来的Bin log的执行情况 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;通过show processlist可以查看主从库用于复制的相关进程(在windows上实际实现为线程)的信息 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show processlist\G </div><div class="line">mysql&gt; CHANGE MASTER TO </div><div class="line"> -&gt; MASTER_HOST=<span class="string">'master_host_name'</span>, </div><div class="line"> -&gt; MASTER_USER=<span class="string">'replication_user_name'</span>, </div><div class="line"> -&gt; MASTER_PASSWORD=<span class="string">'replication_password'</span>, </div><div class="line"> -&gt; MASTER_LOG_FILE=<span class="string">'recorded_log_file_name'</span>, </div><div class="line"> -&gt; MASTER_LOG_POS=recorded_log_position; </div><div class="line">```          </div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;注意：从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME-relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。 </span></div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;为了保证以后binglog及时写入，将主库sync_binlog变量设置1。 </span></div><div class="line"></div><div class="line">----</div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;MYSQL主从同步时出现”Client requested master to start replication from impossible position”错误的解决方法: </span></div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;这个错误是因为从服务器请求一个错误的位置而引起的.比如主服务器上的BLIN LOG里没有这个POSITION. </span></div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;解决原理: </span></div><div class="line"></div><div class="line">&amp;<span class="comment">#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;&amp;#160;从相关的BIN LOG 里最后一个日志位置,与从服务器上的日志对比,如果LOG里最后位置比错误的位置大,那么,说明中间可能有断点,需要把从服务器的位置向前调,多试几次.如果在LOG里最后的位置比从服务器指出的位置还小,那么只要把从服务器的位置设置成LOG的最后位置就可以了. </span></div><div class="line"></div><div class="line">```bash</div><div class="line">mysqlbinlog mysql-log-bin.000112 &gt;log.sql </div><div class="line">tail -f log.sql </div><div class="line"><span class="comment"># at 568380594 </span></div><div class="line"><span class="comment">#110616  3:10:16 server id 1  end_log_pos 568380757     Query   thread_id=123899        exec_time=0     error_code=0 </span></div><div class="line">use thecheap_topshoppinguscom/*!*/; </div><div class="line">SET TIMESTAMP=1308226216/*!*/; </div><div class="line">DELETE FROM `thecheap_topshoppinguscom`.`zm_sessions` WHERE expiry &lt; 1308224416 </div><div class="line">/*!*/; </div><div class="line">DELIMITER ; </div><div class="line"><span class="comment"># End of log file </span></div><div class="line">ROLLBACK /* added by mysqlbinlog */; </div><div class="line">/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;发现这个位置比从服务器上的位置(568397810)还小,只要设置成最后位置,然后启动SLAVE就可以了. </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">CHANGE MASTER TO </div><div class="line">MASTER_LOG_FILE=’mysql-log-bin.000112′, </div><div class="line">MASTER_LOG_POS=568380594;</div></pre></td></tr></table></figure>
<hr>
<blockquote>
<p>–read-only选项<br>该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新，可以确保从服务器不接受来自客户的更新。在测试过程中遇到了read-only的问题，发现写操作因为read-only这个选项的开启，而不能够成功执行。而通过带super权限的用户执行带修改性质的语句时，是能成功执行的。查找了下资料，才了解read-only的真正含义和用法：<br>–read_only  Make all non-temporary tables read-only, with the exception for replication (slave) threads and users with the SUPER privilege.</p>
</blockquote>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SUPER privilege : </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;The SUPER privilege enables an account to use CHANGE MASTER TO , KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS , configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached. </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;To create or alter stored routines if binary logging is enabled, you may also need the SUPER privilege, as described in Section 18.6, “Binary Logging of Stored Programs” . </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;read-only选项：对所有的非临时表进行只读控制。但是有两种情况例外： </p>
<ol>
<li>对replication threads例外，以保证slave能够正常的进行replication。 </li>
<li>对于拥有super权限的用户，可以ignore这个选项。 </li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;SUPER 权限 ： </p>
<ol>
<li>可以有change master to, kill其他用户的线程的权限。 </li>
<li>Purge binary logs 来删除binary log, set global来动态设置变量的权限。 </li>
<li>执行mysqladmin debug命令，开启或者关闭log，在read-only打开时执行update/insert操作。 </li>
<li>执行start slave, stop slave. </li>
<li>当连接数已经达到max_connections的最大值时，也可以连接到server。 </li>
</ol>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql 主从同步状态不一致问题</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;方法一：是强制性从某一个点开始同步，会有部分没有同步的数据丢失，后续主服务器上删除记录同步也会有一些错误信息，不会影响使用。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;方法二：是设置’set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;’，但这样做不一定会有效果。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主从不能同步:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">show slave status;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;报错:Error xxx dosn’t exist</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">show slave status\G:</div><div class="line">Slave_SQL_Running: NO</div><div class="line">Seconds_Behind_Master: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">stop slave;</div><div class="line"><span class="built_in">set</span> global sql_slave_skip_counter =1 ;</div><div class="line">start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;之后Slave会和Master去同步 主要看:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Seconds_Behind_Master是否为0，0就是已经同步了</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还需要做的一些优化与监视:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">show full processlist; //查看mysql当前同步线程号</div><div class="line">skip-name-resolve       //跳过dns名称查询，有助于加快连接及同步的速度</div><div class="line">max_connections=1000    //增大Mysql的连接数目，(默认100)</div><div class="line">max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看日志一些命令</p>
<ol>
<li>show master status\G;<br>在这里主要是看log-bin的文件是否相同。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在这里主要是看:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running=Yes</div><div class="line">Slave_SQL_Running=Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果都是Yes,则说明配置成功.</p>
<ol>
<li>在master上输入show processlist\G;</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SHOW PROCESSLIST\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">Id: 2</div><div class="line">User: root</div><div class="line">Host: localhost:32923</div><div class="line">db: NULL</div><div class="line">Command: Binlog Dump</div><div class="line">Time: 94</div><div class="line">State: Has sent all binlog to slave; waiting <span class="keyword">for</span> binlog to be updated</div><div class="line">Info: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果出现Command: Binlog Dump,则说明配置成功.</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">stop slave    <span class="comment">#停止同步</span></div><div class="line">start slave    <span class="comment">#开始同步，从日志终止的位置开始更新。</span></div><div class="line">SET SQL_LOG_BIN=0|1  <span class="comment">#主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误</span></div><div class="line">SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  <span class="comment"># 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。</span></div><div class="line">RESET MASTER  <span class="comment">#主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER</span></div><div class="line">RESET SLAVE   <span class="comment">#从机运行，清除日志同步位置标志，并重新生成master.info</span></div><div class="line">虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，</div><div class="line">LOAD TABLE tblname FROM MASTER <span class="comment">#从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值</span></div><div class="line">LOAD DATA FROM MASTER  <span class="comment">#从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值</span></div><div class="line">CHANGE MASTER TO master_def_list  <span class="comment">#在线改变一些主机设置，多个用逗号间隔,比如</span></div><div class="line">CHANGE MASTER TO</div><div class="line">  MASTER_HOST=<span class="string">'master2.mysql.com'</span>,</div><div class="line">  MASTER_USER=<span class="string">'replication'</span>,</div><div class="line">  MASTER_PASSWORD=<span class="string">'password'</span></div><div class="line">MASTER_POS_WAIT() <span class="comment">#从机运行</span></div><div class="line">SHOW MASTER STATUS <span class="comment">#主机运行，看日志导出信息</span></div><div class="line">SHOW SLAVE HOSTS <span class="comment">#主机运行，看连入的从机的情况。</span></div><div class="line">SHOW SLAVE STATUS (slave)</div><div class="line">SHOW MASTER LOGS (master)</div><div class="line">SHOW BINLOG EVENTS [ IN <span class="string">'logname'</span> ] [ FROM pos ] [ LIMIT [offset,] rows ]</div><div class="line">PURGE [MASTER] LOGS TO <span class="string">'logname'</span> ; PURGE [MASTER] LOGS BEFORE <span class="string">'date'</span></div></pre></td></tr></table></figure>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;清理binlog日志文件</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;发现放数据库的分区磁盘激增了40多G，一路查看下来，发现配置好主从复制以来到现在的binlog就有40多G，原来根源出在这里，查看了一下 my.cnf，看到binlog的size是1G就做分割，但没有看到删除的配置，在mysql里show了一下variables，</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show variables like <span class="string">'%log%'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查到了</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">| expire_logs_days                | 0                                     |</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个默认是0，也就是logs不过期，这个是一个global的参数，所以需要执行</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">set</span> global expire_logs_days=8;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这样8天前的log就会被删除了，如果有回复的需要，请做好备份工作，但这样设置还不行，下次重启mysql了，配置又恢复默认了，所以需在my.cnf中设置：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">expire_logs_days = 8</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;想要恢愎数据库以前的资料，执行：show binlog events;</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;由于数据量很多，查看起来很麻烦，所以应该适当删除部分可不用的日志。并且如果使用的时间足够长的话，会把我的硬盘空间都给吃掉。</p>
<ol>
<li>登录系统，/usr/bin/mysql</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用mysql查看日志</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show binary logs;</div><div class="line">+—————-+———–+</div><div class="line">| Log_name         | File_size |</div><div class="line">+—————-+———–+</div><div class="line">| mysql-bin.000001 | 150462942 |</div><div class="line">| mysql-bin.000002 | 120332942 |</div><div class="line">| mysql-bin.000003 | 141462942 |</div><div class="line">+—————-+———–+</div></pre></td></tr></table></figure>
<ol>
<li>删除bin-log(删除mysql-bin.000003之前的而没有包含mysql-bin.000003)</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; purge binary logs to ‘mysql-bin.000003′;</div><div class="line"></div><div class="line">Query OK, 0 rows affected (0.16 sec)</div></pre></td></tr></table></figure>
<ol>
<li>查询结果(现在只有一条记录了.)</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show binlog events\G</div><div class="line"></div><div class="line">mysql&gt; show binary logs;</div><div class="line">+—————-+———–+</div><div class="line">| Log_name       | File_size |</div><div class="line">+—————-+———–+</div><div class="line">| mysql-bin.000003 |       106 |</div><div class="line">+—————-+———–+</div><div class="line"></div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;(删除的其它格式运用!)</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">PURGE &#123;MASTER | BINARY&#125; LOGS TO ‘log_name’</div><div class="line">PURGE &#123;MASTER | BINARY&#125; LOGS BEFORE ‘date’</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志，这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。例如：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">PURGE MASTER LOGS TO ‘mysql-bin.010′;</div><div class="line">PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00′;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;清除3天前的 binlog</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要清理日志，需按照以下步骤：</p>
<ol>
<li>在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。</li>
<li>使用SHOW MASTER LOGS获得主服务器上的一系列日志。</li>
<li>在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。</li>
<li>制作您将要删除的所有日志的备份。</li>
<li>清理所有的日志，但是不包括目标日志。</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面讲一下怎么从二进制文件恢复数据, 假如不小心执行了drop table xxx_db, 假如你保留了完整的二进制日志的话, 先不要冒汗, 这是可以恢复的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;先看看日志</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">&gt;mysqlbinlog /diskb/bin-logs/xxx_db-bin.000001</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;找到执行create table xxx_db之后和drop table xxx_db之前的position, 假如是20, 1000.</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">&gt;mysqlbinlog --start-position=<span class="string">"4"</span> --stop-position=<span class="string">"1000"</span> /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还有一种办法是根据日期来恢复</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">&gt;mysqlbinlog --start-datetime=<span class="string">"2010-09-14 0:20:00"</span> --stop-datetim=<span class="string">"2010-09-15 01:25:00"</span> /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果相关的语句不在同一个binlog文件里，则需要从不同的文件来恢复。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们。下面是一个说明什么是不安全的例子：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">shell&gt; mysqlbinlog hostname-bin.000001 | mysql <span class="comment"># DANGER!!</span></div><div class="line">shell&gt; mysqlbinlog hostname-bin.000002 | mysql <span class="comment"># DANGER!!</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。当第2个mysql进程想使用该表时，服务器报告 “不知道该表”。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">shell&gt; mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">shell&gt; mysqlbinlog hostname-bin.000001 &gt;  /tmp/statements.sql</div><div class="line">shell&gt; mysqlbinlog hostname-bin.000002 &gt;&gt; /tmp/statements.sql</div><div class="line">shell&gt; mysql -e <span class="string">"source /tmp/statements.sql"</span></div></pre></td></tr></table></figure>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql主从重新同步’binlog’日志</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;binlog中有一个位置(position)变量，可用于控制其工作进程。</p>
<ol>
<li>从’slave’在某个’position’之后停止同步</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">START SLAVE UNTIL MASTER_LOG_FILE=<span class="string">'xxxxx'</span>, MASTER_LOG_POS=yyyyyy;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：在执行前要确定从库的同步已停。</p>
<ol>
<li>重新从某一’position’后同步数据</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;笔者所碰到的一个现象：在一台繁忙的主库上，其传输过来的语句在从库没有完全得到执行，从而导致一分多钟的数据丢失(这么多年第一次碰到，事后通过分析binlog日志文件才发现，从库的状态居然是正常的)。<br>STOP SLAVE;</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">change master to master_host=<span class="string">'master'</span>,master_user=<span class="string">'user'</span>,master_password=<span class="string">'passwd'</span>,master_log_file=<span class="string">'mysql-bin.file'</span>,master_log_pos=prev_position;</div><div class="line">START SLAVE;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这样可以从出问题的时间点靠前一点的’position’开始重新同步’sql‘操作。但这样会报错，因为之前有数据存在了。诸如此类：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Last-Errno: 1062</div><div class="line">Last-Error: Error <span class="string">'Duplicate entry '</span>15386<span class="string">' for key 1'</span> on query. Default database: <span class="string">'db'</span>. Query: <span class="string">'INSERT INTO db.table ( FIELDS ) VALUES ( VALUES )'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;键重复了，必须跳过才能继续。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">stop slave; <span class="built_in">set</span> global sql_slave_skip_counter=1; start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者在’my.cnf’文件中指定该错误跳过，</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">--slave-skip-errors=xxx,yyy</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;须重启服务器使其生效。常见问题及操作：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ <span class="keyword">for</span> key %d</div><div class="line"></div><div class="line">You can skip also other <span class="built_in">type</span> of errors, but again don’t <span class="keyword">do</span> this unless you understand very well what those queries are and what impact they have on your data:</div><div class="line"></div><div class="line">slave-skip-errors=[err_code1,err_code2,...|all]</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;报错相关的更多信息，请参考：<a href="http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html" target="_blank" rel="external">http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html</a></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：如果’xxx’为’all’的话，则表示跳过所有错误并继续，但这并不是个好的建议。这就很必要将从库设定为只读(read_only)且用非特权用户来访问它。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;官方文档有关于它的(replication-slave)更多参考：<a href="http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html" target="_blank" rel="external">http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html</a></p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q. No argument was provided to –log-bin, and –log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use ‘–log-bin=mysql-bin’ to avoid this problem.</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A: 在my.cnf 的[mysqld]中加入 log-bin=mysql-bin</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q. Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=mysql-relay-bin’ to avoid this problem.</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A: 在my.cnf 的[mysqld]中加入 relay-log=mysql-relay-bin</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q. [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A: 在my.cnf 的[mysqld]中加入 replicate-same-server-id</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q.[Note] Slave I/O thread: connected to master ‘test@:3306′,replication started in log ‘FIRST’ at position 4</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;[ERROR] Error reading packet from server: Access denied; you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227)</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A: 可能原来使用过slave链接.需要将mysql库中的master.info删除重新</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q：如果主服务器正在运行并且不想停止主服务器，怎样配置一个从服务器？</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A：有多种方法。如果你在某时间点做过主服务器备份并且记录了相应快照的二进制日志名和偏移量(通过SHOW MASTER STATUS命令的输出)，采用下面的步骤： </p>
<ol>
<li>确保从服务器分配了一个唯一的服务器ID号。 </li>
<li>在从服务器上执行下面的语句，为每个选项填入适当的值： </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; CHANGE MASTER TO </div><div class="line"> -&gt;MASTER_HOST=<span class="string">'master_host_name'</span>, </div><div class="line"> -&gt;MASTER_USER=<span class="string">'master_user_name'</span>, </div><div class="line"> -&gt;MASTER_PASSWORD=<span class="string">'master_pass'</span>, </div><div class="line"> -&gt;MASTER_LOG_FILE=<span class="string">'recorded_log_file_name'</span>, </div><div class="line"> -&gt;MASTER_LOG_POS=recorded_log_position;</div></pre></td></tr></table></figure>
<ol>
<li>在从服务器上执行START SLAVE语句。 </li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。 </p>
<ol>
<li>发出该语句： </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; FLUSH TABLES WITH READ LOCK；</div></pre></td></tr></table></figure>
<ol>
<li>仍然加锁时，执行该命令(或它的变体)： </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">shell&gt; tar zcf /tmp/backup.tar.gz /var/lib/mysql</div></pre></td></tr></table></figure>
<ol>
<li>发出该语句并且确保记录了以后用到的输出： </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt;SHOW MASTER STATUS；</div></pre></td></tr></table></figure>
<ol>
<li>释放锁： </li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; UNLOCK TABLES；</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一个可选择的方法是，转储主服务器的SQL来代替前面步骤中的二进制复制。要这样做，你可以在主服务器上使用mysqldump –master-data，以后装载SQL转储到到你的从服务器。然而，这比进行二进制复制速度慢。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不管你使用这两种方法中的那一个，当你有一个快照和记录了日志名与偏移量时，后来根据说明操作。你可以使用相同的快照建立多个从服务器。一旦你拥有主服务器的一个快照，可以等待创建一个从服务器，只要主服务器的二进制日志完整。两个能够等待的时间实际的限制是指在主服务器上保存二进制日志的可用硬盘空间和从服务器同步所用的时间。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;你也可以使用LOAD DATA FROM MASTER。这是一个方便的语句，它传输一个快照到从服务器并且立即调整日志名和偏移量。将来，LOAD DATA FROM MASTER将成为创建从服务器的推荐方法。然而需要注意，它只工作在MyISAM 表上并且可能长时间持有读锁定。它并不象我们希望的那样高效率地执行。如果你有大表，执行FLUSH TABLES WITH READ LOCK语句后，这时首选方法仍然是在主服务器上制作二进制快照。 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Q：从服务器需要始终连接到主服务器吗？</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;A：不，不需要。从服务器可以宕机或断开连接几个小时甚至几天，重新连接后获得更新信息。例如，你可以在通过拨号的链接上设置主服务器/从服务器关系，其中只是偶尔短时间内进行连接。这意味着，在任何给定时间，从服务器不能保证与主服务器同步除非你执行某些特殊的方法。将来，我们将使用选项来阻塞主服务器直到有一个从服务器同步。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;slave-skip-errors #是跳过错误，继续执行复制操作</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主服务器上的相关命令：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">show master status</div><div class="line">show slave hosts</div><div class="line">show logs</div><div class="line">show binlog events</div><div class="line">purge logs to <span class="string">'log_name'</span></div><div class="line">purge logs before <span class="string">'date'</span></div><div class="line">reset master(老版本flush master)</div><div class="line"><span class="built_in">set</span> sql_log_bin=</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从服务器上的相关命令:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">slave start</div><div class="line">slave stop</div><div class="line">SLAVE STOP IO_THREAD //此线程把master段的日志写到本地</div><div class="line">SLAVE start IO_THREAD</div><div class="line">SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库</div><div class="line">SLAVE start SQL_THREAD</div><div class="line">reset slave</div><div class="line">SET GLOBAL SQL_SLAVE_SKIP_COUNTER</div><div class="line">load data from master</div><div class="line">show slave status(SUPER,REPLICATION CLIENT)</div><div class="line">CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息</div><div class="line">PURGE MASTER [before <span class="string">'date'</span>] 删除master端已同步过的日志</div><div class="line">6.3.1 Master 同步线程状态</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也就是说，没有slave连接上来。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Sending binlog event to slave</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Finished reading one binlog; switching to next binlog</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;读取完了一个二进制日志，正切换到下一个。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Has sent all binlog to slave; waiting for binlog to be updated</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新<br>的事件，然后读取它们。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">Waiting to finalize termination</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当前线程停止了，这个时间很短。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;6.3.2 Slave的I/O线程状态</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。</p>
<p>Connecting to master</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该线程证尝试连接到master上。</p>
<p>Checking master version</p>
<p>确定连接到master后出现的一个短暂的状态。</p>
<p>Registering slave on master</p>
<p>确定连接到master后出现的一个短暂的状态。</p>
<p>Requesting binlog dump</p>
<p>确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。</p>
<p>Waiting to reconnect after a failed binlog dump request</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由 </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;–master-connect-retry 选项来指定。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Reconnecting after a failed binlog dump request</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该线程正尝试重连到master。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting for master to send event</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒<br>，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Queueing master event to the relay log</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting to reconnect after a failed master event read</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Reconnecting after a failed master event read</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting for the slave SQL thread to free enough relay log space</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间<br>。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting for slave mutex on exit</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当前线程停止了，这个时间很短。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;6.3.3 Slave的SQL线程状态</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下列出了slave的SQL线程 State 字段中最常见的几种状态：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Reading event from the relay log</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从中继日志里读到一个事件以备执行。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Has read all relay log; waiting for the slave I/O thread to update it</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting for slave mutex on exit</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当前线程停止了，这个时间很短。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;常见的一些问题：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一.从库SLAVE启动问题</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;由于一些错误操作导致 CHANGE MASTER 和 SLAVE 服务无法启动，系统报错如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">*****************************************************************</div><div class="line">Could not initialize master info structure; more error messages can be found <span class="keyword">in</span> the MySQL error <span class="built_in">log</span>.</div><div class="line">*****************************************************************</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;无法初始化master info结构，MySQL错误日志记录了更详细的错误信息。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法：</p>
<ol>
<li>查看MySQL错误日志，如：同步的上一个Position是多少，很多情况下无法启动服务是由于mysql识别的同步始终停留在上一个Position上。</li>
<li>查看master.info和relay-log.info，master.info 记录MASTER相关信息，relay-log.info 记录当前同步日志信息。</li>
<li>停止myslq服务，删除master.info和relay-log.info。</li>
<li>启动mysql服务。</li>
<li>重新CHANGE MASTER，重新启动SLAVE服务。</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;二.主从不能同步</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;show slave status;报错:Error xxx dosn’t exist,且</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">show slave status\G:</div><div class="line">Slave_SQL_Running: NO</div><div class="line">Seconds_Behind_Master: NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决方法:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">stop slave;</div><div class="line"><span class="built_in">set</span> global sql_slave_skip_counter =1 ;</div><div class="line">start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;之后Slave会和Master去同步 主要看:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Seconds_Behind_Master是否为0，0就是已经同步了</p>
<ol>
<li>还需要做的一些优化与监视:</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">show full processlist; //查看mysql当前同步线程号</div><div class="line">skip-name-resolve       //跳过dns名称查询，有助于加快连接及同步的速度</div><div class="line">max_connections=1000    //增大Mysql的连接数目，(默认100)</div><div class="line">max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看日志一些命令</p>
<ol>
<li>show master status\G;</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在这里主要是看log-bin的文件是否相同。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在这里主要是看:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running=Yes</div><div class="line">Slave_SQL_Running=Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果都是Yes,则说明配置成功.</p>
<ol>
<li>在master上输入show processlist\G;</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SHOW PROCESSLIST\G</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果出现Command: Binlog Dump,则说明配置成功.</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">stop slave    <span class="comment">#停止同步</span></div><div class="line">start slave    <span class="comment">#开始同步，从日志终止的位置开始更新。</span></div><div class="line">SET SQL_LOG_BIN=0|1  <span class="comment">#主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误</span></div><div class="line">SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  <span class="comment"># 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。</span></div><div class="line">RESET MASTER  <span class="comment">#主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER</span></div><div class="line">RESET SLAVE   <span class="comment">#从机运行，清除日志同步位置标志，并重新生成master.info</span></div><div class="line">虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，</div><div class="line">LOAD TABLE tblname FROM MASTER <span class="comment">#从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值</span></div><div class="line">LOAD DATA FROM MASTER  <span class="comment">#从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值</span></div><div class="line">CHANGE MASTER TO master_def_list  <span class="comment">#在线改变一些主机设置，多个用逗号间隔，比如：</span></div><div class="line">CHANGE MASTER TO</div><div class="line">MASTER_HOST=<span class="string">'master2.mycompany.com'</span>,</div><div class="line">MASTER_USER=<span class="string">'replication'</span>,</div><div class="line">MASTER_PASSWORD=<span class="string">'bigs3cret'</span></div><div class="line">MASTER_POS_WAIT() <span class="comment">#从机运行</span></div><div class="line">SHOW MASTER STATUS <span class="comment">#主机运行，看日志导出信息</span></div><div class="line">SHOW SLAVE HOSTS <span class="comment">#主机运行，看连入的从机的情况。</span></div><div class="line">SHOW SLAVE STATUS (slave)</div><div class="line">SHOW MASTER LOGS (master)</div><div class="line">SHOW BINLOG EVENTS [ IN <span class="string">'logname'</span> ] [ FROM pos ] [ LIMIT [offset,] rows ]</div><div class="line">PURGE [MASTER] LOGS TO <span class="string">'logname'</span> ; PURGE [MASTER] LOGS BEFORE <span class="string">'date'</span></div><div class="line">show binlog events; <span class="comment">#查看主库二进制日志文件内容：</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意：</p>
<ol>
<li><p>主辅库同步主要是通过二进制日志来实现同步的。</p>
</li>
<li><p>在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用，因为读取的还是master.info文件里的信息。</p>
</li>
</ol>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;补充：从服务器上my.cnf中的master-*的设置仅在第一次生效，后保存在master.info文件里。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从服务器上使用show slave status</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Slave_IO_Running,为No,则说明IO_THREAD没有启动，请执行slave start [IO_THREAD]</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Slave_SQL_Running为No则复制出错,查看Last_error字段排除错误后执行slave start [SQL_THREAD]</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看Slave_IO_State字段</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;空 //复制没有启动</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Connecting to master//没有连接上master</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Waiting for master to send event//已经连上</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以使用LOAD DATA FROM MASTER语句来建立slave，但有约束条件：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权限。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在master端执行RESET MASTER清除已有的日志变更，此时slave端会因为找不到master日志无法启动IO_THREAD，请清空data目录下</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;relay-log.info,hosname-relay-bin*等文件重新启动mysql</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的–</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;relay-log和–relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;–relay-log-info-file启动选项修改文件名。双机互备则是两个mysql同时配置为master及slave</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主服务器上的相关命令：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">show master status</div><div class="line">show slave hosts</div><div class="line">show &#123;master|binary&#125; logs</div><div class="line">show binlog events</div><div class="line">purge &#123;master|binary&#125; logs to <span class="string">'log_name'</span></div><div class="line">purge &#123;master|binary&#125; logs before <span class="string">'date'</span></div><div class="line">reset master(老版本flush master)</div><div class="line"><span class="built_in">set</span> sql_log_bin=&#123;0|1&#125;</div></pre></td></tr></table></figure>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从服务器上的相关命令:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">slave start</div><div class="line">slave stop</div><div class="line">SLAVE STOP IO_THREAD //此线程把master段的日志写到本地</div><div class="line">SLAVE start IO_THREAD</div><div class="line">SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库</div><div class="line">SLAVE start SQL_THREAD</div><div class="line">reset slave</div><div class="line">SET GLOBAL SQL_SLAVE_SKIP_COUNTER</div><div class="line">load data from master</div><div class="line">show slave status(SUPER,REPLICATION CLIENT)</div><div class="line">CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息</div><div class="line">PURGE MASTER [before <span class="string">'date'</span>] 删除master端已同步过的日志</div></pre></td></tr></table></figure>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mysql的Relay Log无法自动删除</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;发现其数据目录下的relay-log 长期没有删除，已经堆积了几十个relay-log。然而其他作为Slave服务器实例却没有这种情况，综合分析后发现和以下原因有关。</p>
<ul>
<li>该实例原先是一个Slave   ——-导致relay-log 和 relay-log.index的存在</li>
<li>该实例目前已经不是Slave  ——-由于没有了IO-Thread，导致relay-log-purge 没有起作用( 这也是其他Slave实例没有这种情况的原因，因为IO-thread会做自动rotate操作)。</li>
<li>该实例每天会进行日常备份  ——-Flush logs的存在，导致每天会生成一个relay-log</li>
<li>该实例没有配置expire-logs-days ——导致flush logs时，也不会做relay-log清除</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;简而言之就是： 一个实例如果之前是Slave，而之后停用了(stop slave)，且没有配置expire-logs-days的情况下，会出现relay-log堆积的情况。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;顺带也和大家分享下MySQL内部Logrotate的机制</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Binary Log rotate机制：</p>
<ul>
<li>Rotate：每一条binary log写入完成后，都会判断当前文件是否超过 max_binlog_size，如果超过则自动生成一个binlog file。</li>
<li>Delete：expire-logs-days 只在 实例启动时 和 flush logs 时判断，如果文件访问时间早于设定值，则purge file。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Relay Log rotate 机制：</p>
<ul>
<li>Rotate：每从Master fetch一个events后，判断当前文件是否超过 max_relay_log_size 如果超过则自动生成一个新的relay-log-file。</li>
<li>Delete：purge-relay-log 在SQL Thread每执行完一个events时判断，如果该relay-log 已经不再需要则自动删除。</li>
<li>Delete：expire-logs-days 只在 实例启动时 和 flush logs 时判断，如果文件访问时间早于设定值，则purge file  (同Binlog file)  (注意: expire-logs-days和relaylog的purge没有关系)。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;因此建议当slave不再使用时，通过reset slave来取消relaylog，以免出现relay-log堆积的情况。</p>
<hr>
<p>s&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;lave的主从信息变更引起的错误</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">130311 14:15:46  mysqld started</div><div class="line">130311 14:15:46 [Warning] option <span class="string">'read_rnd_buffer_size'</span>: unsigned value 0 adjusted to 8200</div><div class="line">130311 14:15:47  InnoDB: Started; <span class="built_in">log</span> sequence number 15 2381115047</div><div class="line">130311 14:15:47 [ERROR] Failed to open the relay <span class="built_in">log</span> <span class="string">'./localhost-relay-bin.000037'</span> (relay_log_pos 52474065)</div><div class="line">130311 14:15:47 [ERROR] Could not find target <span class="built_in">log</span> during relay <span class="built_in">log</span> initialization</div><div class="line">130311 14:15:47 [ERROR] Failed to initialize the master info structure</div><div class="line">130311 14:15:47 [Note] /usr/<span class="built_in">local</span>/mysql/libexec/mysqld: ready <span class="keyword">for</span> connections.</div><div class="line">Version: <span class="string">'5.0.87-log'</span>  socket: <span class="string">'/var/tmp/mysql2.sock'</span>  port: 3308  Source distribution</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;对于这类问题解决起来是比较简单的，重置slave的参照即可。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">mysql&gt; reset slave;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; change master to ....</div><div class="line">ERROR 29 (HY000): File <span class="string">'/data/mysqldata/3306/binlog/mysql-relay-bin.000001'</span> not found (Errcode: 2)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看来应该还是mysql-relay-bin.index的问题，删除该文件及关联的relay-bin文件。再次配置master：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to ....</div><div class="line">ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found <span class="keyword">in</span> the MySQL error <span class="built_in">log</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;出现了新的错误，按照提示查看error_log也没发现更多错误信息，error_log中只是显示一条：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">120326 11:14:27 [ERROR] Error reading master configuration</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在操作系统端查看master/slave的配置文件，发现是两个0字节文件：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">-rw-rw---- 1 mysql mysql     0 Mar 26 11:13 master.info</div><div class="line">-rw-rw---- 1 mysql mysql     0 Mar 26 11:13 relay-log.info</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;会不会是这个原因呢，直接删除这两个文件，然后尝试重新执行change master：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to ....</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;成功，启动slave并查看状态：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">mysql&gt; start slave;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; show slave status\G</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;故障解决。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;磁盘故障</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">Warning: a page <span class="keyword">in</span> the doublewrite buffer is not within space</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;出现此种报错，多为磁盘故障。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;master不能初始化</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;ERROR 1201 (HY000): Could not initialize master解决方法 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to master_host=<span class="string">'192.168.1.10'</span>,master_user=<span class="string">'replication'</span>,master_password=<span class="string">'123456'</span>,master_log_file=<span class="string">'freeoa_log.000003'</span>,master_log_pos=106;</div><div class="line"></div><div class="line">ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found <span class="keyword">in</span> the MySQL error <span class="built_in">log</span></div><div class="line"></div><div class="line">mysql&gt; slave stop;</div><div class="line">Query OK, 0 rows affected, 1 warning (0.00 sec)</div><div class="line">mysql&gt; reset slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;出现这个问题的原因，应该是以前mysql做过主从。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/23. mysql主从复制/">mysql 主从复制</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="mysql复制"><a href="#mysql复制" class="headerlink" title="mysql复制"></a>mysql复制</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL复制支持单向，异步复制。通过一台主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。MySQL主从复制是异步进行的。同步需要版本为5.5,使用google提供的插件来实现。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL主从复制操作很灵活既可以实现整个服务的级别的复制，也可以实现对某个库，甚至某个数据库中的指定的某个对象进行复制。</p>
<h2 id="MySQL主从复制应用场景"><a href="#MySQL主从复制应用场景" class="headerlink" title="MySQL主从复制应用场景"></a>MySQL主从复制应用场景</h2><ol>
<li>提高性能。通过一(多)主多从的部署方案，将涉及数据写的操作放在Master端操作，而将数据读的操作分散到众多的Slave当中。降低了Master的负载，提高数据写入的响应效率；多台从服务器提供读，分担了请求，提高了读的效率。</li>
<li>数据安全。数据复制到Slave节点，即使Master宕机，Slave节点还保存着完整数据。</li>
<li>数据分析。将数据分析，放在slave上。</li>
</ol>
<h2 id="主从复制的原理"><a href="#主从复制的原理" class="headerlink" title="主从复制的原理"></a>主从复制的原理</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL的Replication是一个异步的复制过程，从一个MySQL实例(Master)复制到另一台MySQL实例上。在Master和Slave之间复制的整个过程主要由3个线程完成，其中两个线程（SQL线程和IO线程）在Slave端，另外一个线程(IO线程)在Master端。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要实现主从复制，首先要在Master端打开Binary Log功能。因为整个复制过程实际上    就是Slave从Master上获取二进制日志，然后在自己身上完全按照产生的顺序一次执行日志中记录的各种操作的过程。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制的具体过程如下：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/01.jpeg?raw=true" alt=""></figure></p>
<ol>
<li>Slave的IO线程连上Master，并请求日志文件指定位置(或从开始的日志)之后的日志的内容。</li>
<li>Master接收到来自Slave的IO线程请求后，负责复制IO线程根据请求的信息读取指定日志之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息，还包含了包括本次返回的信息在Master端的Binary Log文件的名称和位置。</li>
<li>Slave的IO线程接受到信息后，将日志内容一次写入Slave端的Relay Log文件(mysql-relay-bin.xxxx)的末端，并将读取到的Master端的bin-log的文件和位置记录到master-info文件中，以便在下一次读取时能够清楚地告诉Master，下次从bin-log哪个位置开始往后的日志内容。</li>
<li>Slave的SQL线程检测检测到Relay Log中更新内容后，会马上解析该Log文件中的内容，还原成在Master端真实执行时的可执行的SQL语句，并执行这些SQK语句。实际上Master和Slave执行同样的语句。</li>
</ol>
<h2 id="Binary-Log-记录方式"><a href="#Binary-Log-记录方式" class="headerlink" title="Binary Log 记录方式"></a>Binary Log 记录方式</h2><h3 id="Row-Level"><a href="#Row-Level" class="headerlink" title="Row Level"></a>Row Level</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Binary Log会记录成每一行数据被修改的形式，然后在Slave端再对相同的数据进行修改。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>优点</strong>：在Row Level模式下，Binnary Log可以不记录执行的Query语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。Row Level会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或Function，以及Trigger的调用和触发无法被正确复制问题。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>缺点</strong>：产生大量的日志内容。</p>
<h3 id="Statment-Level"><a href="#Statment-Level" class="headerlink" title="Statment Level"></a>Statment Level</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;每一条会修改的SQL语句都会记录到Master的Binnary中。Slave端在复制的时候，SQL线程会解析成和原来Master端执行过相同的SQL语句，并再次执行。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>优点</strong>：首先，解决了Row Level下的缺点，不须要记录每一行的数据变化，减少了Binnary Log日志量，节约了IO成本，提高了性能。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>缺点</strong>：由于它是记录的执行语句，为了让这些语句在Slave端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息，即上下文信息，以保证所有语句在Slave端被执行的时候能够得到和在Master端执行时相同的结果。另外，由于MySQL发展比较快，很多新功能不断加入，使得MySQL复制遇到了不小的挑战，复制时设计的内容岳父在，越容易出bug。在Statement Level下，目前已发现不少的情况下会造成MySQL的复制问题。主要是在修改数据使用了某些特定的函数货功能后，出现，比如：Sleep()函数在有些版本中就不能正确的复制，在存储过程中使用了last_insert_id()函数，可能会使Slave和Master的到不一致的ID，等等。</p>
<h3 id="Mixed-Level"><a href="#Mixed-Level" class="headerlink" title="Mixed Level"></a>Mixed Level</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在Mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。除了MySQL认为通过Statement方式可能造成复制过程中Master和Slave之间产生不一致数据。(如特殊Procedure和Funtion的使用，UUID()函数的使用等特殊情况)时，它会选择ROW的模式来记录变更之外，都会使用Statement方式。</p>
<h2 id="设置主从"><a href="#设置主从" class="headerlink" title="设置主从"></a>设置主从</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主从设置的主要步骤是</p>
<ol>
<li>修改Master和Slave的my.cnf中的server-id，使之唯一,开启binlog。</li>
<li>若不停Master时，加入全局锁，记录bin-log文件和bin-log文件的位置，全备要同步的数据库；解除全局锁</li>
<li>若可以停库时，停止主库，物理备份所需要同步的数据库。</li>
<li>在Slave端恢复备份的数据。</li>
<li>用change master在Slave建立与master的联系。</li>
<li>启动Slave。</li>
<li>检查Slave的状态。</li>
</ol>
<h3 id="实例"><a href="#实例" class="headerlink" title="实例"></a>实例</h3><ol>
<li>不停主库的建立主从复制。</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>第一部分 对主库的操作</strong></p>
<ol>
<li>修改主库的文件，开启MySQL的bin-log。（一般安装的时候最好先做好，这样可以不停库。）<br>修改主库的配置文件my.cnf：</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">vim /etc/my.cnf</div><div class="line"></div><div class="line">server-id = 1 <span class="comment"># 设置server-id 确保id为唯一，最好用ip地址，后三位</span></div><div class="line">bin-log=mysql-bin <span class="comment"># 设置 bin-log，这地方可以指定为bin-log的目录？</span></div><div class="line"></div><div class="line">/etc/init.d/mysqld restart</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改完成后，重启mysql</p>
<ol>
<li>登录主库，添加从库的同步账号。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql –uroot –p</div><div class="line">grant replication slave on *.* to <span class="string">'rep'</span>@<span class="string">'%'</span> identified by <span class="string">'passwd'</span>;</div></pre></td></tr></table></figure>
<ol>
<li>对主库进行锁表操作，禁止更新，（为了防止备份时的数据不一致问题）。并查看bin-log的名字和日志的（position）。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">flush tables with <span class="built_in">read</span> lock；</div><div class="line">show master status；</div><div class="line"></div><div class="line">mysql&gt; show master status;</div><div class="line">+------------------+----------+--------------+------------------+</div><div class="line">| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |</div><div class="line">+------------------+----------+--------------+------------------+</div><div class="line">| mysql-bin.000107 | 38874616 |              |                  |</div><div class="line">+------------------+----------+--------------+------------------+</div><div class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;记录这两个值</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql-bin.000107</div><div class="line">38874616</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;取得<code>bin-log</code> 和 <code>position</code></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -uroot -p<span class="string">'yz11235'</span> -e <span class="string">"show master status"</span> | awk <span class="string">'&#123;if (NR == 2)&#123; print $1 "\n" $2;&#125;&#125;'</span> &gt; temp.txt</div></pre></td></tr></table></figure>
<ol>
<li>对主库进行全备份。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -h hostname -uroot -p’’ -A -B -F | gzip &gt;alldb.sql.gz</div></pre></td></tr></table></figure>
<ol>
<li>导出数据库后，进行对数据库表解锁。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">unlock tables</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>第二部分 从库上操作</strong></p>
<ol>
<li>在从库上设置my.cnf，设置server-id，和注释bin-log。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">server-id = 2</div><div class="line"><span class="comment"># bin-log = mysql-bin</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启 mysql。</p>
<ol>
<li>登录从库，并设置从库信息</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">CHANGE MASTER TO </div><div class="line">MASTER_HOST=<span class="string">'10.143.39.174'</span>,</div><div class="line">MASTER_PORT=3306,</div><div class="line">MASTER_USER=<span class="string">'rep'</span>,</div><div class="line">MASTER_PASSWORD=<span class="string">'passwd'</span>,</div><div class="line">MASTER_LOG_FILE=<span class="string">'mysql-bin.000003'</span>,</div><div class="line">MASTER_LOG_POS=151348020,</div><div class="line">MASTER_CONNECT_RETRY=10;</div></pre></td></tr></table></figure>
<ol>
<li>启动 从库</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">start slave;</div></pre></td></tr></table></figure>
<ol>
<li>查看从库状态，</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">show slave status\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;观察：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">slave_IO_Running : Yes</div><div class="line">Slave_SQL_Running : Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上两个为Yes 说明主从已经成功。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">Second_behind_master = 0</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是从库落后主库的秒数。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/22. mysql主从搭建/">mysql 主从搭建</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql 主从 （mysql replication），主要用于 mysql 的实时备份或者读写分离。在配置之前做准备工作，配置两台 mysql 服务器；或者在同一个机器上跑两个 mysql 服务。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql 主从原理非常简单:</p>
<ul>
<li>每个从仅可以设置一个主；</li>
<li>主在执行 sql 之后，记录二进制 log 文件 （bin-log）；</li>
<li>从连接主，并从主获取 binlog ，存于本地 relay-log ，并从上次记住的位置起执行 sql，一旦遇到错误则停止同步。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从这几条 replication 原理来看，可以推论：</p>
<ul>
<li>主从间的数据库不是实时同步，就算网络正常连接，也存在瞬间，主从数据不一致；</li>
<li>如果主从网络断开，从会在网路正常后，批量同步；</li>
<li>如果对从进行修改数据，那么很可能从在执行主的 bin-log 时出现错误而停止同步，这是很危险的操作。所以一般情况下，非常小心的修改从上的数据。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一个衍生的配置是双主，互为主从配置，只要双方不修改冲突，可以工作良好。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以应用在读写分离的场景中，用以降低单台 mysql 服务器的 I/O </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以实现 mysql 服务的 HA 集群。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以是一主多从，也可以是相互主从（主主）。</p>
<h2 id="在同一台机器上配置-MySQL-主从"><a href="#在同一台机器上配置-MySQL-主从" class="headerlink" title="在同一台机器上配置 MySQL 主从"></a>在同一台机器上配置 MySQL 主从</h2><h3 id="安装、配置-mysql（mysql版本5-1-40）"><a href="#安装、配置-mysql（mysql版本5-1-40）" class="headerlink" title="安装、配置 mysql（mysql版本5.1.40）"></a>安装、配置 mysql（mysql版本5.1.40）</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先在同一台机器上配置两个 MySQL 服务（跑两个端口）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># cd /usr/local</span></div><div class="line">[root@192 <span class="built_in">local</span>]<span class="comment"># cp -r mysql mysql_slave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;拷贝配置文件</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 <span class="built_in">local</span>]<span class="comment"># cd mysql_slave/</span></div><div class="line">[root@192 mysql_slave]<span class="comment"># cp /etc/my.cnf ./my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件相关参数</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># vim my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;更改 port 为 3307 ， sock 为 /tmp/mysql_slave.sock 以及 datadir </p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/01.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;初始化 mysql_slave</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动准备</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># cp /etc/init.d/mysqld /etc/init.d/mysqldslave</span></div><div class="line">[root@192 mysql_slave]<span class="comment"># vim /etc/init.d/mysqldslave</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/02.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/32.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/03.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;最好在前边添加</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/04.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 mysql_slave]<span class="comment"># /etc/init.d/mysqldslave start</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/05.png?raw=true" alt=""></figure></p>
<h3 id="配置主从准备工作"><a href="#配置主从准备工作" class="headerlink" title="配置主从准备工作"></a>配置主从准备工作</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设定 /usr/local/mysql 为主，端口 3306，/usr/local/mysql_slave 为从，端口为 3307 。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录主 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -h127.0.0.1 -P3306</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -h127.0.0.1 -P3307</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上创建测试数据库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建数据库 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create database db1;</div><div class="line">Query OK, 1 row affected (0.03 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后导出主的 mysql 数据库谈后导入给 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysqldump -S /tmp/mysql.sock mysql &gt; 1.sql</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql.sock db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<h3 id="配置主（master）"><a href="#配置主（master）" class="headerlink" title="配置主（master）"></a>配置主（master）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改或添加：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/06.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两个可选参数（二选一）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">binlog-do-db=db1    <span class="comment">#用来指定需要同步的库，这里只同步 db1</span></div><div class="line">binlog-ignore-db=db1,db2    <span class="comment">#指定忽略不同步的库，这里不同步 db1,db2</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件后，重启 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设置 root 密码</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysqladmin -uroot -S /tmp/mysql.sock password '123456'</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -uroot -S /tmp/mysql.sock -p123456</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后授权给从一个用来同步数据的用户 repl</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'repl'</span>@<span class="string">'127.0.0.1'</span> identified by <span class="string">'123123'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只给授权 replication 权限；用户名 repl ；由于是本机，所有用回还地址；密码是123123</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重新刷新权限</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush privileges;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把表的读锁死</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush tables with <span class="built_in">read</span> lock;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要记住前两列的内容，后面会用到</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/07.png?raw=true" alt=""></figure></p>
<h3 id="设置从"><a href="#设置从" class="headerlink" title="设置从"></a>设置从</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># vim /usr/local/mysql_slave/my.cnf</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改或添加</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/08.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个数值不能和主一样</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可选参数 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">replicate-do-db=db1,db2</div><div class="line">replicate-ignore-db=db1,db2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启 从的 mysqld 服务</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqldslave restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上创建 db1 数据库，拷贝住的 db1 数据库到从</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock -e "create database db1"</span></div><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># mysql -S /tmp/mysql_slave.sock</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行命令</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave stop;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to master_host=<span class="string">'127.0.0.1'</span>, master_port=3306, master_user=<span class="string">'repl'</span>, master_password=<span class="string">'123123'</span>, master_log_file=<span class="string">'mysql-bin.000001'</span>, master_log_pos=453</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;指定主的 host 127.0.0.1 ；主的 post 3306；读主的用户 repl ，密码 123123；</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; slave start;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看配置是否成功</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/09.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;slave 两个显示都是 YES 表示成功</p>
<h3 id="测试主从"><a href="#测试主从" class="headerlink" title="测试主从"></a>测试主从</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解锁表的读</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; unlock tables;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上删除一个表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use db1;</div><div class="line">Database changed</div><div class="line">mysql&gt; show tables;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/10.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;删除表 help_category</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop table help_category;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/11.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再到从上查看</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/12.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上清空 db1 库的 db 表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use db1</div><div class="line"></div><div class="line">mysql&gt; select count(*) from db;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/13.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; truncate table db;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/14.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入 slave ，查看 db1 库 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/15.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;得出的结果为 0 了，说明主从是同步的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在主上删除表 db</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop table db;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上看 db 表不存在了</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/16.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也可以创建一个表</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show create table user\G;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;复制这个表，然后创建表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/17.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/18.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show tables;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看发现多了 表 yanyi</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/19.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从上查看</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/20.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;建议：MySQL 主从机制比较脆弱，谨慎操作、如果重启 master ，务必要先把 slave 停掉，也就是说需要在 slave 上去执行 slave stop 命令，然后再去重启 master 的 mysql 服务，否则很有可能就会中断了，当然重启完成后，还需要把 slave 给开启 slave start 。 </p>
<h2 id="在两台机器上配置-MySQL-主从"><a href="#在两台机器上配置-MySQL-主从" class="headerlink" title="在两台机器上配置 MySQL 主从"></a>在两台机器上配置 MySQL 主从</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;安装、配置 MySQL （mysql 版本5.7.17）</p>
<h3 id="配置主从准备工作-1"><a href="#配置主从准备工作-1" class="headerlink" title="配置主从准备工作"></a>配置主从准备工作</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两台机器 192.168.0.72 和192.168.0.71  </p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设定 192.168.0.72 为主 192.168.0.71 为从</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上创建测试数据库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456</span></div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create database db1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456 -e "create database db1;"</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后导出主的 mysql 库数据，然后导入给 db1</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># mysqldump -uroot -p123456 mysql &gt; 1.sql</span></div><div class="line">[root@master ~]<span class="comment"># mysql -uroot -p123456 db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/21.png?raw=true" alt=""></figure></p>
<h3 id="配置主（master）-1"><a href="#配置主（master）-1" class="headerlink" title="配置主（master）"></a>配置主（master）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@master ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/22.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/23.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;两个可选参数（二选一）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">binlog-do-db=db1    <span class="comment">#用来指定需要同步的库，这里只同步 db1</span></div><div class="line">binlog-ignore-db=db1,db2    <span class="comment">#指定忽略不同步的库，这里不同步 db1,db2</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改配置文件后，重启 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@192 ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后授权给从一个用来同步数据的用户 repl</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant replication slave on *.* to <span class="string">'repl'</span>@<span class="string">'192.168.0.71'</span> identified by <span class="string">'123123'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只给授权 replication 权限；用户名 repl ；授权机器 IP 192.168.0.71；密码是123123</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重新刷新权限</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush privileges;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把表的读锁死</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush tables with <span class="built_in">read</span> lock;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;要记住前两列的内容，后面会用到</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show master status;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/24.png?raw=true" alt=""></figure></p>
<h3 id="设置从（slave）"><a href="#设置从（slave）" class="headerlink" title="设置从（slave）"></a>设置从（slave）</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># vim /etc/my.cnf</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/25.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改为</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/26.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个数值不能和主一样</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可选参数 </p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">replicate-do-db=db1,db2</div><div class="line">replicate-ignore-db=db1,db2</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启 从的 mysqld 服务</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># /etc/init.d/mysqld restart</span></div><div class="line">Shutting down MySQL.. SUCCESS! </div><div class="line">Starting MySQL. SUCCESS!</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上创建 db1 数据库，拷贝主的 db1 数据库到从</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456 -e "create database db1;"</span></div><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456 db1 &lt; 1.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录从 mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@slave ~]<span class="comment"># mysql -uroot -p123456</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行如下命令</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; stop slave;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; change master to master_host=<span class="string">'192.168.0.72'</span>, master_port=3306, master_user=<span class="string">'repl'</span>, master_password=<span class="string">'123123'</span>, master_log_file=<span class="string">'mysql-bin.000001'</span>, master_log_pos=600;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;指定主的 host 192.168.0.72 ；主的 post 3306；读主的用户 repl ，密码 123123；</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; start slave;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;从上查看状态</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show slave status\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/27.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看是否显示</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">Slave_IO_Running: Yes</div><div class="line">Slave_SQL_Running: Yes</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只有两个同时 YES ,才算正常。</p>
<h3 id="测试主从-1"><a href="#测试主从-1" class="headerlink" title="测试主从"></a>测试主从</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在主上清空 db1 库的 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/28.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;进入 从，查看 db1 库 db 表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/29.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;得出结论为 0 了，说明主从同步的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在主删除表 db </p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/30.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在从上查看 db 表不存在了</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%20%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/31.png?raw=true" alt=""></figure></p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/21. 同一台MySQL服务器启动多个端口/">同一台MySQL服务器启动多个端口</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先安装二进制源码包的mysql</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;安装到初始化数据库的时候，因为是多个端口，所以要根据配置文件来初始化多个数据库。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;比如有2个端口</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;则要运行两次</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">./scripts/mysql_install_db --datadir=/home/mysql1 --user=mysql</div><div class="line">./scripts/mysql_install_db --datadir=/home/mysql2 --user=mysql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;编辑配置文件</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div></pre></td><td class="code"><pre><div class="line">[mysqld0]</div><div class="line">port = 3300</div><div class="line">socket = /tmp/mysql0.sock</div><div class="line">pid-file = /home/mysql0/localhost.localdomain0.pid</div><div class="line">datadir = /home/mysql0</div><div class="line"><span class="comment">#log = /data/mysql0/mysql0.log</span></div><div class="line">user = mysql</div><div class="line">skip-locking</div><div class="line">skip-name-resolve</div><div class="line"><span class="comment">#skip-bdb</span></div><div class="line"><span class="comment">#skip-innodb</span></div><div class="line">key_buffer = 128M</div><div class="line">max_allowed_packet = 1M</div><div class="line">table_cache = 864</div><div class="line">sort_buffer_size = 1M</div><div class="line">read_buffer_size = 512K</div><div class="line">read_rnd_buffer_size = 1M</div><div class="line">myisam_sort_buffer_size = 32M</div><div class="line">thread_cache_size = 16</div><div class="line">query_cache_size = 32M</div><div class="line">thread_concurrency = 8</div><div class="line"><span class="comment">#skip-networking</span></div><div class="line">wait_timeout=8</div><div class="line">max_connections=512</div><div class="line">max_connect_errors = 10000000</div><div class="line">max_user_connections=20</div><div class="line"><span class="comment">#slow_queries=/data/mysql0slowquer.sql</span></div><div class="line"><span class="comment">#log_slow_queries=/data/mysql0slowquer.sql</span></div><div class="line"><span class="comment">#long_query_time=3</span></div><div class="line"><span class="comment">#log-bin=mysql0-bin</span></div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div></pre></td><td class="code"><pre><div class="line">[mysqld1]</div><div class="line">port = 3301</div><div class="line">socket = /tmp/mysql1.sock</div><div class="line">pid-file = /home/mysql1/localhost.localdomain1.pid</div><div class="line">datadir = /home/mysql1</div><div class="line"><span class="comment">#log = /data/mysql1/mysql1.log</span></div><div class="line">user = mysql</div><div class="line">skip-locking</div><div class="line">skip-name-resolve</div><div class="line"><span class="comment">#skip-innodb</span></div><div class="line"><span class="comment">#skip-bdb</span></div><div class="line">key_buffer = 128M</div><div class="line">max_allowed_packet = 1M</div><div class="line">table_cache = 864</div><div class="line">sort_buffer_size = 1M</div><div class="line">read_buffer_size = 512K</div><div class="line">read_rnd_buffer_size = 1M</div><div class="line">myisam_sort_buffer_size = 32M</div><div class="line">thread_cache_size = 16</div><div class="line">query_cache_size = 32M</div><div class="line">thread_concurrency = 8</div><div class="line"><span class="comment">#skip-networking</span></div><div class="line">wait_timeout=8</div><div class="line">max_connections=512</div><div class="line">max_connect_errors = 10000000</div><div class="line">max_user_connections=20</div><div class="line"><span class="comment">#log_slow_queries=/data/mysql1slowquer.sql</span></div><div class="line"><span class="comment">#long_query_time=3</span></div><div class="line"><span class="comment">#log-bin=mysql1-bin</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把配置文件放在 /etc/my.cnf</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;启动mysql</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">/usr/<span class="built_in">local</span>/mysql/bin/mysqld_multi start 0-1</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里的0或1是根据配置文件中 <code>[mysqld0]</code> 来定的。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/20.mysql字符集调整总结/">mysql字符集调整总结</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;字符集是一套符号和编码的规则，不论是在oracle数据库还是在mysql数据库，都存在字符集的选择问题。对于数据库来说，字符集又是比较重要的，因为数据库存储的数据大部分都是各种文字，字符集对于数据库的存储、处理性能以及数据迁移都有重要的影响。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果在数据库创建阶段没有正确选择字符集，那么可能在后期需要更换字符集，而字符集的更换是代价比较高的操作，也存在一定的风险，所以我们建议在应用开始阶段，就按照需求正确的选择合适的字符集，尽量避免后期不必要的调整。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql编译安装时，指定字符集的方法：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">./configure  --with-charset=utf8</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql的字符集有4个级别的默认设置：服务器级、数据库级、表级和字段级。分别在不同的地方设置，作用也不相同。</p>
<h2 id="1、服务器字符集设定，在mysql服务启动的时候确定。"><a href="#1、服务器字符集设定，在mysql服务启动的时候确定。" class="headerlink" title="1、服务器字符集设定，在mysql服务启动的时候确定。"></a>1、服务器字符集设定，在mysql服务启动的时候确定。</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以在my.cnf中设置：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">[mysql]</div><div class="line"><span class="comment">### 默认字符集为utf8</span></div><div class="line">default-character-set=utf8</div><div class="line">[mysqld]</div><div class="line"><span class="comment">### 默认字符集为utf8</span></div><div class="line">default-character-set=utf8</div><div class="line"><span class="comment">### （设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）</span></div><div class="line">init_connect=<span class="string">'SET NAMES utf8'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;或者在启动选项中指定：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqld --default-character-set=utf8</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果没有特别的指定服务器字符集，默认使用latin1(ISO-8859-1的别名)作为服务器字符集。上面三种设置的方式都只指定了字符集，没有去做校对，我们可以用show variables like ‘char%’;命令查询当前服务器的字符<br>集和校对规则。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div></pre></td><td class="code"><pre><div class="line">mysql&gt;show variables like <span class="string">'char%'</span>;</div><div class="line">   +--------------------------+----------------------------+</div><div class="line"></div><div class="line">　　| Variable_name | Value |</div><div class="line"></div><div class="line">　　+--------------------------+----------------------------+</div><div class="line"></div><div class="line">　　| character_set_client | utf8 |</div><div class="line"></div><div class="line">　　| character_set_connection | utf8 |</div><div class="line"></div><div class="line">　　| character_set_database | utf8 |</div><div class="line"></div><div class="line">　　| character_set_filesystem | binary |</div><div class="line"></div><div class="line">　　| character_set_results | utf8 |</div><div class="line"></div><div class="line">　　| character_set_server | utf8 |</div><div class="line"></div><div class="line">　　| character_set_system | utf8 |</div><div class="line"></div><div class="line">　　| character_sets_dir | /usr/share/mysql/charsets/ |</div><div class="line"></div><div class="line">　　+--------------------------+----------------------------+</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：如果增加default-character-set=utf8后，MYSQL启动报错。可以用character_set_server=utf8来取代default-character-set=utf8，就能正常启动了。这是因为MYSQL不同版本识别的问题。</p>
<h2 id="2、数据库级"><a href="#2、数据库级" class="headerlink" title="2、数据库级"></a>2、数据库级</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建数据库时指定字符集</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt;CREATE DATABASE my_db default charset utf8 COLLATE utf8_general_ci;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意后面这句话 “COLLATE utf8_general_ci”,大致意思是在排序时根据utf8编码格式来排序<br>如果指定了数据库编码，那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改MYSQL数据库编码，如果是MYSQL数据库编码不正确，可以在MYSQL执行如下命令:</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">ALTER DATABASE my_db DEFAULT CHARACTER SET utf8;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上命令就是将MYSQL的my_db数据库的编码设为utf8</p>
<h2 id="3、表级"><a href="#3、表级" class="headerlink" title="3、表级"></a>3、表级</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建表时指定字符集</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt;create table my_table (name varchar(20) not null default <span class="string">''</span>)<span class="built_in">type</span>=myisam default charset utf8;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这句话就是创建一个表,指定默认字符集为utf8</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改MYSQL表的编码：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">ALTER TABLE my_table DEFAULT CHARACTER SET utf8;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上命令就是将一个表my_table的编码改为utf8</p>
<h2 id="4、字段级"><a href="#4、字段级" class="headerlink" title="4、字段级"></a>4、字段级</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">alter table <span class="built_in">test</span> add column address varchar(110) after stu_id;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在stu_id后增加一个字段address</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">alter table <span class="built_in">test</span> add id int unsigned not Null auto_increment primary key;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改字段的编码：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">ALTER TABLE `<span class="built_in">test</span>` CHANGE `name` `name` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上命令就是将MYSQL数据库test表中name的字段编码改为utf8</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在命令行下插入汉字时如下代码：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">set</span> names utf8;有时候这一句很关键！</div><div class="line">insert into charset values(<span class="string">'王达'</span>);</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意：alter修改的方法不能更新已有记录的字符集，只是对新创建的表和记录生效。对已有记录字符集的调整，需要先将数据导出，经过适当调整后重新导入才可以完全修改编码。</p>
<h2 id="导出导入的字符调整方法"><a href="#导出导入的字符调整方法" class="headerlink" title="导出导入的字符调整方法"></a>导出导入的字符调整方法</h2><h3 id="导出表结构"><a href="#导出表结构" class="headerlink" title="导出表结构"></a>导出表结构</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -uroot -pmysql --default-character-set=latin1 -d  my_db&gt; createtab.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;手工修改createtab.sql表结构定义中的字符集为新的字符集</p>
<h4 id="导出所有记录"><a href="#导出所有记录" class="headerlink" title="导出所有记录"></a>导出所有记录</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -uroot -pmysql --quick --no-create-info --extended-insert --default-character-set=latin1 --host=localhost  my_db&gt; data.sql</div></pre></td></tr></table></figure>
<h4 id="打开data-sql，将set-names-latin1修改成set-names-utf8"><a href="#打开data-sql，将set-names-latin1修改成set-names-utf8" class="headerlink" title="打开data.sql，将set names latin1修改成set names utf8"></a>打开data.sql，将set names latin1修改成set names utf8</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">:%s/latin1/utf8/g</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;全文替换</p>
<h4 id="使用新的字符集创建新的数据库"><a href="#使用新的字符集创建新的数据库" class="headerlink" title="使用新的字符集创建新的数据库"></a>使用新的字符集创建新的数据库</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">create database  mydata  default charset utf8;</div></pre></td></tr></table></figure>
<h4 id="创建表，执行createtab-sql"><a href="#创建表，执行createtab-sql" class="headerlink" title="创建表，执行createtab.sql"></a>创建表，执行createtab.sql</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -uroot -pmysql mydata&lt;creattab.sql</div></pre></td></tr></table></figure>
<h4 id="导入数据"><a href="#导入数据" class="headerlink" title="导入数据"></a>导入数据</h4><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -uroot -pmysql mydata&lt;data.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注意一点就是目标字符集要大于等于源字符集，否则会丢失一部分不支持的汉字数据。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;附：旧数据升级办法</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以原来的字符集为latin1为例，升级成为utf8的字符集。原来的表: old_table (default charset=latin1)，新表：new_table(default charset=utf8)。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;第一步：导出旧数据</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table &gt; old.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;第二步：转换编码</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">iconv -t utf8  -f latin1 -c old.sql &gt; new.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在这里，假定原来的数据默认是latin1编码。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;第三步：导入</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改old.sql，增加一条sql语句： “SET NAMES utf8;”，保存。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -hlocalhost -uroot my_db &lt; new.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;大功告成！</p>
<h2 id="Mysql-collate规则："><a href="#Mysql-collate规则：" class="headerlink" title="Mysql collate规则："></a>Mysql collate规则：</h2><ul>
<li>*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的</li>
<li>*_cs: case sensitive collation，区分大小写</li>
<li>*_ci: case insensitive collation，不区分大小写</li>
</ul>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/2. MySQL 5.5 源码安装/">MySQL 5.5 源码安装</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先安装必要的库</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">yum install -y gcc*</div></pre></td></tr></table></figure>
<h2 id="首先安装cmake"><a href="#首先安装cmake" class="headerlink" title="首先安装cmake"></a>首先安装cmake</h2><ol>
<li>支持yum安装</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">yum install -y cmake</div></pre></td></tr></table></figure>
<ol>
<li>也可以源码安装</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/src</div><div class="line"><span class="comment">#下载cmake</span></div><div class="line">wget http://www.cmake.org/files/v2.8/cmake-2.8.7.tar.gz</div><div class="line">tar zxvf cmake-2.8.7.tar.gz</div><div class="line"><span class="built_in">cd</span> cmake-2.8.7</div><div class="line"><span class="comment">#安装cmake</span></div><div class="line">./configure</div><div class="line">make</div><div class="line">make install</div></pre></td></tr></table></figure>
<h2 id="安装-MySQL"><a href="#安装-MySQL" class="headerlink" title="安装 MySQL"></a>安装 MySQL</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<a href="http://dev.mysql.com/downloads" target="_blank" rel="external">官网</a>下载mysql5.5版本Linux系统源码包。</p>
<h3 id="安装"><a href="#安装" class="headerlink" title="安装"></a>安装</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">groupadd mysql</div><div class="line">useradd -g mysql mysql</div><div class="line">tar zxvf mysql-5.2.25.tar.gz</div><div class="line"><span class="built_in">cd</span> mysql-5.2.25</div></pre></td></tr></table></figure>
<ul>
<li>cmake：默认情况下安装，安装目录为/usr/local/mysql ，数据目录为/usr/local/mysql/data</li>
<li>也可以指定参数安装，如指定UTF8，数据引擎等</li>
<li>具体参照<a href="https://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html" target="_blank" rel="external">官方说明文档</a></li>
</ul>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line">cmake -DCMAKE_INSTALL_PREFIX=/usr/<span class="built_in">local</span>/mysql -DMYSQL_DATADIR=/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=all -DWITH_DEBUG=0 -DWITH_SSL=yes -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1</div><div class="line">make &amp;&amp; make install</div><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/mysql</div><div class="line">chown -R mysql:mysql /usr/<span class="built_in">local</span>/mysql</div><div class="line">./scripts/mysql_install_db --user=mysql -datadir=/mysql/data</div><div class="line"><span class="comment">#此处如不指定datadir，到启动时会报错</span></div><div class="line">chown -R root .</div><div class="line">chown -R mysql data</div><div class="line">cp support-files/my-medium.cnf /etc/my.cnf</div><div class="line">bin/mysqld_safe --user=mysql &amp;amp;</div><div class="line"><span class="comment"># Next command is optional</span></div><div class="line">cp support-files/mysql.server /etc/init.d/mysqld</div><div class="line">chmod +x /etc/init.d/mysqld</div><div class="line">/etc/init.d/mysqld start</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;到此，安装完成。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/19. mysqld_multi stop 不能停掉mysql/">mysqld_multi stop 不能停掉mysql</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用mysqld_multi start  启动了多个mysql实例，但是mysqld_multi stop 却不能停止，为啥呢？因为还没有授权呢。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">/usr/<span class="built_in">local</span>/mysql/bin/mysqld_multi stop</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;但是默认是停不掉的，需要做一个授权</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">grant shutdown on *.* to <span class="string">'username'</span>@<span class="string">'localhost'</span> identified by <span class="string">'password'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外还需要在my.cnf配置文件中加上：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">[mysqld_multi]</div><div class="line">mysqld = /usr/<span class="built_in">local</span>/mysql/bin/mysqld_safe</div><div class="line">mysqladmin = /usr/<span class="built_in">local</span>/mysql/bin/mysqladmin</div><div class="line">user = username</div><div class="line">password = password</div></pre></td></tr></table></figure>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/18. innobackupex 备份 Xtrabackup 增量备份/">innobackupex 备份 Xtrabackup 增量备份</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Xtrabackup中包含两个工具：</p>
<ul>
<li>xtrabackup - 用于热备份innodb, xtradb表的工具，不能备份其他表(MYISAM表)。</li>
<li>innobackupex - 对xtrabackup封装的perl脚本，提供了myisam表备份的能力。（能进行整库和数据表备份）。</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：备份恢复之前请做好全库备份</p>
<h2 id="安装Xtrabackup"><a href="#安装Xtrabackup" class="headerlink" title="安装Xtrabackup"></a>安装Xtrabackup</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<a href="http://www.percona.com/doc/percona-xtrabackup/index.html" target="_blank" rel="external">官网网址</a></p>
<h3 id="安装"><a href="#安装" class="headerlink" title="安装"></a>安装</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;配置文件中需要添加 <code>datadir = /usr/local/mysql/datadir</code> ，MYSQL数据文件目录</p>
<h3 id="1-自动安装yum源后，用yum安装"><a href="#1-自动安装yum源后，用yum安装" class="headerlink" title="1.自动安装yum源后，用yum安装"></a>1.自动安装yum源后，用yum安装</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">yum install -y gnupg</div><div class="line">rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm</div><div class="line"> </div><div class="line">yum install -y percona-xtrabackup</div></pre></td></tr></table></figure>
<h3 id="2-手动写入yum源"><a href="#2-手动写入yum源" class="headerlink" title="2.手动写入yum源"></a>2.手动写入yum源</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;新建文件 <code>/etc/yum.repos.d/Percona.repo</code></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">[percona]</div><div class="line">name = CentOS <span class="variable">$releasever</span> - Percona</div><div class="line">baseurl=http://repo.percona.com/centos/<span class="variable">$releasever</span>/os/<span class="variable">$basearch</span>/</div><div class="line">enabled = 1</div><div class="line">gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona</div><div class="line">gpgcheck = 1</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;之后yum安装 ，安装后可执行 <code>xtrabackup -v</code> 查看</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;之后可以用xtrabackup 备份</p>
<h2 id="innobackupex-备份全库"><a href="#innobackupex-备份全库" class="headerlink" title="innobackupex 备份全库"></a>innobackupex 备份全库</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;备份主程序为 /usr/bin/innobackupex-1.5.1，其需要从 mysql 配置文件中读取相关信息，Mysql缺省配置文件 my.cnf 中未配置 datadir 选项，必须显性添加，否则备份程序会报错：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">1innobackupex:: Warning: Ignored unrecognized line 2 <span class="keyword">in</span> options : <span class="string">'xtrabackup: Error: Please set parameter '</span>datadir<span class="string">'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在Mysql 配置文件 <code>/etc/my.cnf</code> 配置文件添加 <code>datadir</code> 内容：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在[mysqld]段加入</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">datadir = /usr/<span class="built_in">local</span>/mysql/var</div></pre></td></tr></table></figure>
<h3 id="1-备份"><a href="#1-备份" class="headerlink" title="1.备份"></a>1.备份</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#/usr/bin/innobackupex-1.5.1 --user=root --password=password --defaults-file=/etc/my.cnf /usr/local/bbsBackup</span></div></pre></td></tr></table></figure>
<h3 id="2-恢复"><a href="#2-恢复" class="headerlink" title="2.恢复"></a>2.恢复</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">/usr/bin/innobackupex-1.5.1 --apply-log /usr/<span class="built_in">local</span>/bbsBackup/2011-09-26_02-00-01/</div><div class="line">/usr/bin/innobackupex-1.5.1 --copy-back /usr/<span class="built_in">local</span>/bbsBackup/2011-09-26_02-00-01/</div><div class="line">chown -R mysql:mysql /usr/<span class="built_in">local</span>/mysql/</div><div class="line">/etc/init.d/mysqld start</div></pre></td></tr></table></figure>
<h2 id="全量备份及恢复"><a href="#全量备份及恢复" class="headerlink" title="全量备份及恢复"></a>全量备份及恢复</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：使用xtrabackup，仅限InnoDB和xtradb表，且注意mysql配置文件my.cnf中需设置“<code>default_table_type = InnoDB</code>”否则不成功</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base/</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;恢复时执行两次：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base</div><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#将数据库停掉</span></div><div class="line">/etc/init.d/mysqld stop</div><div class="line"><span class="comment">#删除数据库目录下的ib*（ib开头的所有）文件。</span></div><div class="line">rm /usr/<span class="built_in">local</span>/mysql/var/ib*</div><div class="line"><span class="comment">#将/usr/local/bbsBackup/base目录下的ib*文件拷贝到数据库目录。</span></div><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/mysql/var/</div><div class="line">cp /usr/<span class="built_in">local</span>/bbsBackup/base/ib* ./</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设置权限：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">chown mysql:mysql ib*</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启数据库后测试，是否成功。</p>
<h2 id="增量备份及恢复"><a href="#增量备份及恢复" class="headerlink" title="增量备份及恢复"></a>增量备份及恢复</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：做增量前当然要先进行全量备份，在全量的基础上来进行增量。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先进行全量备份。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base/</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在全量备份的基础上进行增量。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/1 --incremental-basedir=/usr/<span class="built_in">local</span>/bbsBackup/base/</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;注：/usr/local/bbsBackup/1是每次都需修改的。比如第二次增量就改成/usr/local/bbsBackup/2增量恢复。（步骤同全量恢复，只是在执行恢复命令的时候中间多一步）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base</div><div class="line">/usr/bin/xtrabackup --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base --prepare --incremental-dir=/usr/<span class="built_in">local</span>/bbsBackup/1</div><div class="line">/usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/usr/<span class="built_in">local</span>/bbsBackup/base</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#将数据库停掉</span></div><div class="line">/etc/init.d/mysqld stop</div><div class="line"><span class="comment">#删除数据库目录下的ib*（ib开头的所有）文件。</span></div><div class="line">rm /usr/<span class="built_in">local</span>/mysql/var/ib*</div><div class="line"><span class="comment">#将/usr/local/bbsBackup/base目录下的ib*文件拷贝到数据库目录。</span></div><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/mysql/var/</div><div class="line">cp /usr/<span class="built_in">local</span>/bbsBackup/base/ib* ./</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;设置权限：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">chown mysql:mysql ib*</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启数据库后测试，是否成功。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/17. Mysql各种存储引擎的特性以及如何选择存储引擎/">Mysql各种存储引擎的特性以及如何选择存储引擎</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="几个常用存储引擎的特点"><a href="#几个常用存储引擎的特点" class="headerlink" title="几个常用存储引擎的特点"></a>几个常用存储引擎的特点</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面我们重点介绍几种常用的存储引擎并对比各个存储引擎之间的区别和推荐使用方式。</p>
<table>
<thead>
<tr>
<th style="text-align:center">特点</th>
<th style="text-align:center">Myisam</th>
<th style="text-align:center">BDB</th>
<th style="text-align:center">Memory</th>
<th style="text-align:center">InnoDB</th>
<th style="text-align:center">Archive</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">存储限制</td>
<td style="text-align:center">没有</td>
<td style="text-align:center">没有</td>
<td style="text-align:center">有</td>
<td style="text-align:center">64TB</td>
<td style="text-align:center">没有</td>
</tr>
<tr>
<td style="text-align:center">事务安全</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">锁机制</td>
<td style="text-align:center">表锁</td>
<td style="text-align:center">页锁</td>
<td style="text-align:center">表锁</td>
<td style="text-align:center">行锁</td>
<td style="text-align:center">行锁</td>
</tr>
<tr>
<td style="text-align:center">B树索引</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">哈希索引</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">全文索引</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">集群索引</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">数据缓存</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">索引缓存</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td style="text-align:center">数据可压缩</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">支持</td>
</tr>
<tr>
<td style="text-align:center">空间使用</td>
<td style="text-align:center">低</td>
<td style="text-align:center">低</td>
<td style="text-align:center">N/A</td>
<td style="text-align:center">高</td>
<td style="text-align:center">非常低</td>
</tr>
<tr>
<td style="text-align:center">内存使用</td>
<td style="text-align:center">低</td>
<td style="text-align:center">低</td>
<td style="text-align:center">中等</td>
<td style="text-align:center">高</td>
<td style="text-align:center">低</td>
</tr>
<tr>
<td style="text-align:center">批量插入的速度</td>
<td style="text-align:center">高</td>
<td style="text-align:center">高</td>
<td style="text-align:center">高</td>
<td style="text-align:center">低</td>
<td style="text-align:center">非常高</td>
</tr>
<tr>
<td style="text-align:center">支持外键</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;支持</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
</tr>
</tbody>
</table>
<h2 id="最常使用的2种存储引擎："><a href="#最常使用的2种存储引擎：" class="headerlink" title="最常使用的2种存储引擎："></a>最常使用的2种存储引擎：</h2><ul>
<li>Myisam是Mysql的默认存储引擎。当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。   </li>
<li>InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。</li>
</ul>
<h2 id="如何选择合适的存储引擎"><a href="#如何选择合适的存储引擎" class="headerlink" title="如何选择合适的存储引擎"></a>如何选择合适的存储引擎</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;选择标准：根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面是常用存储引擎的适用环境：</p>
<ol>
<li>MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一</li>
<li>InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。</li>
<li>Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。</li>
<li>Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。</li>
</ol>

	

	

</article>





	<span class="different-posts">📖 <a href="/page/30">more posts</a> 📖</span>



	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
